CODEWITHSUNDEEP
sqlsql-servert-sql
Ajay Unnikrishnan
Ajay Unnikrishnan

Reputation: 129

Query to combine column values

Need help in forming SQL Query

CREATE TABLE #TempTable(
        [ID] [int] NULL,
        [Source] char(1) NULL,
        [Destination] char(1) NULL
    ) 

insert into #TempTable values(1,'A','B')
insert into #TempTable values(1,'B','C')
insert into #TempTable values(1,'C','D')
insert into #TempTable values(2,'A','B')

Trying for a tsql query to get the following output:

ID  CombinedValues
1   A,B,C,D
2   A,B

Please not that I need the 'Destination' column of last row.

Upvotes: 0

Views: 72

Answers (2)

Esat Erkec
Esat Erkec

Reputation: 1724

    DECLARE @LastID AS INT
    SELECT @LastID=MAX(ID) FROM #TempTable

    DROP TABLE IF EXISTS #TempTbl2
    SELECT  * INTO #TempTbl2 FROM   
    (
    SELECT * FROM (
    SELECT ID,SOURCE FROM #TempTable WHERE ID<>@LastID
    UNION ALL
    SELECT ID,Destination FROM #TempTable WHERE ID<>@LastID
    ) AS TMP_TBL 
    GROUP BY ID,SOURCE
    ) AS TMP_TBL2

    select ID,
    STRING_AGG( ISNULL(Source, ' '), ',') AS CombinedValues from #TempTbl2 WHERE ID<>@LastID
    GROUP  BY ID 
    UNION ALL
    SELECT TOP 1 ID,source from #TempTable WHERE ID=@LastID 

+----+----------------+
| ID | CombinedValues |
+----+----------------+
|  1 | A,B,C,D        |
|  2 | A              |
+----+----------------+

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

the query below, uses row_number() to identify the first row from the rest. Only for first row, the Source is added for concatenation

; with cte as
(
    select  ID, [Source], [Destination], 
        r = row_number() over (partition by ID order by [Source])
    from    #TempTable
)
select  ID,
    ( select case when r = 1 then [Source] else '' end 
          + ',' + [Destination] 
      from  cte x 
      where x.ID = t.ID for xml path('')
     ) as CombinedValues
from    cte t
group by ID

Upvotes: 1

Related Questions