1

For example, I have a table of:

id | code | name | type | deviceType
---+------+------+------+-----------
1  | 23   | xyz  | 0    | web
2  | 23   | xyz  | 0    | mobile
3  | 24   | xyzc | 0    | web
4  | 25   | xyzc | 0    | web

I want the result to be:

id | code | name | type | deviceType
---+------+------+------+-----------
1  | 23   | xyz  | 0    | web&mobile
2  | 24   | xyzc | 0    | web
3  | 25   | xyzc | 0    | web

How do I do this in SQL Server using UPDATE and DELETE statements?

Note: Unfortunately I can't use the solutions given because SQL server 2014 does not have 'STRING_AGG' function.

Any help is greatly appreciated!

2 Answers 2

1

please, try this:

with stable as (
SELECT
    ROW_NUMBER() over(order by [code]) as [ID]
    , [code]
    , [name]
    , [type]
    , CASE
        WHEN (SUM([web]) = 1 AND SUM([mobile]) = 1) THEN 'web&mobile'
        WHEN (SUM([web]) = 1 ) THEN 'web'
        WHEN (SUM([mobile]) = 1) THEN 'mobile'
        ELSE null END AS deviceType
FROM (
    select [id], [code], [name], [type], [web], [mobile]
    from (
        SELECT [id], [code], [name], [type], [deviceType] FROM [dbo].[table]
    ) as source
    pivot 
    (
        COUNT(deviceType) FOR deviceType IN ([web],[mobile])
    ) as tb
) source
group by [code], [name], [type])
,ttable as (
SELECT [id], [code], [name], [type], [deviceType] FROM [dbo].[table]
)
MERGE
    INTO ttable as T
    USING stable as S
    ON t.id = S.id
    WHEN MATCHED THEN
        UPDATE SET
            T.[code] = S.[code], T.[name] = S.[name], T.[type] = S.[type], T.[deviceType] = S.[deviceType]
    WHEN NOT MATCHED BY SOURCE THEN
        DELETE 
;
1

Assuming you want to group by code, name, and type fields and get the aggregation of deviceType's, in SQL Server 2014 you can use this workaround of stuff and FOR XML. This is generic and will work with any values in the deviceType field.

One difference in this solution compared to your example output is the original rows/id's in the table get updated and extra ones removed. In your example, id 1 and 2 are combined, so the final result keeps row 1 which is now 1 and 2, and removes id 2 as a separate row.

Here's a fully reproducible example verified in SQL Server 2014:

create table #tmp (
    id int not null,
    code int not null,
    name nvarchar(255) not null,
    type int not null,
    deviceType nvarchar(255) not null)

insert into #tmp values (1, 23, 'xyz', 0, 'web')
insert into #tmp values (2, 23, 'xyz', 0, 'mobile')
insert into #tmp values (3, 24, 'xyzc', 0, 'web')
insert into #tmp values (4, 25, 'xyzc', 0, 'web')

-- Original table for comparison
select * from #tmp

-- Output grouped by code, name, type, and aggregate deviceType
;with results as (
    select
    min(id) as id,
    code, 
    name, 
    type,
    ( 
        stuff((SELECT '&' + Cast(dt.deviceType AS VARCHAR (300)) [text()]
            FROM   #tmp dt
            WHERE  tmp.code = dt.code
                and tmp.name = dt.name
                and tmp.type = dt.type
            FOR xml path(''),
            type).value('.', 'nvarchar(MAX)'), 1, 1, '')
    ) as [deviceType]
    from #tmp tmp
    group by code, name, type
)
merge into #tmp
using results on #tmp.id = results.id
when matched then
    update set #tmp.deviceType = results.deviceType
when not matched by source then
    delete;

select * from #tmp

drop table #tmp

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.