Combine different rows to one using the similar column values of one column-CodePudding

I am looking to merge all the columns based on similar specific column value.

Suppose i have similar values in "DeckName" , so using Column2 i am looking out to merge all the distinct other column values into one .

Input Table :

DeckId     DeckName   Location   Items     Places   UserName

XK001      NOVA        USA       Cream     ASIA-P   NOVA001
XK002      NOVA        IND       DEO       AFRICA   NOVA002
XK001      NOVA        USA       Cosmetic  ASIA-P   NOVA003
VK001      VEET        RUS       PROFIX    UK       VEET001
VK003      VEET        CHI       Cream     ASIA-P   VEET002
VK002      VEET        NED       WAX       RUSSIA   VEET001
NS001      PHLIPS      USA       Cream     ASIA-P   PHLPS001
PS001      RUDS        USA       Cream     ASIA-P   VLT001

Expected Output Table Value

 DeckId                DeckName   Location       Items                 Places              UserName
XK001; XK002           NOVA       USA; IND       Cream; DEO; Cosmetic  ASIA-P; AFRICA      NOVA001; NOVA002; NOVA003
VK001; VK003; VK002    VEET       RUS; CHI; NED  PROFIX; Cream; WAX    UK; ASIA-P; RUSSIA  VEET001; VEET002
NS001                  PHLIPS     USA            Cream                 ASIA-P              PHLPS001
PS001                  RUDS       USA            Cream                 ASIA-P              VLT001

Please Suggest a query to find proceed with the expected outcome :

CodePudding user response：

Are you trying something like :

select STRING_AGG(DeckId,';') as DeckId , 
       DeckName , 
       STRING_AGG(Location,';') as Location,
       STRING_AGG(Items,';') as Items,
       STRING_AGG(Places,';') as Places,
       STRING_AGG(UserName,';') as UserName
from  test_tbl
group by DeckName;

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ece626d2ce2919c2148cccbe83e4634e

Check for more info: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15

For Older SQL Server Version , maybe not the best solution but you can try:

SELECT DISTINCT ST2.DeckName, 
    SUBSTRING(
        (
            SELECT ';' DeckId  AS [text()]
            FROM test_tbl ST1
            WHERE ST1.DeckName = ST2.DeckName
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 9999) [DeckId],
        
      SUBSTRING(
        (
            SELECT ';' Location  AS [text()]
            FROM test_tbl ST1
            WHERE ST1.DeckName = ST2.DeckName
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 9999) [Location]  ,
        
      SUBSTRING(
        (
            SELECT ';' Items  AS [text()]
            FROM test_tbl ST1
            WHERE ST1.DeckName = ST2.DeckName
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 9999) [Items]  , 
        
      SUBSTRING(
        (
            SELECT ';' Places  AS [text()]
            FROM test_tbl ST1
            WHERE ST1.DeckName = ST2.DeckName
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 9999) [Places]  ,
        
     SUBSTRING(
        (
            SELECT ';' UserName  AS [text()]
            FROM test_tbl ST1
            WHERE ST1.DeckName = ST2.DeckName
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 9999) [UserName]  
        
FROM test_tbl ST2

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=53aef222b43a50f6e4874e3b307a018b

CodePudding user response：

Which version of SQL server are you using ?

you can use string_agg function in sql server (same as listagg in Oracle) if you are using 2017 and above. Something like below -

select deck_name, 
        STRING_AGG(deck_id, ';') within group(order by deck_name) as deck_id,
        STRING_AGG(Location, ';') within group(order by deck_name) as location,
        STRING_AGG(items, ';') within group(order by deck_name) as items,
        STRING_AGG(places, ';') within group(order by deck_name) as place,
        STRING_AGG(username, ';') within group(order by deck_name) as username
from deck_table dt
group by deck_name

for older version, you will have to use the STUFF function with XML path. I cant run it and you may have to play around with the XML expression, but you can try something like below -

select deck_name, 
        STUFF((
       SELECT distinct ';'   dt1.deck_id
         FROM deck_table dt1
        WHERE dt1.deck_name = dt.deck_name
        ORDER BY dt1.deck_id
          FOR XML PATH('')), 1, LEN(','), '') AS deck_id,
          STUFF((
       SELECT distinct ';'   dt1.location
         FROM deck_table dt1
        WHERE dt1.deck_name = dt.deck_name
        ORDER BY dt1.location
          FOR XML PATH('')), 1, LEN(','), '') AS location,
          STUFF((
       SELECT distinct ';'   dt1.items
         FROM deck_table dt1
        WHERE dt1.deck_name = dt.deck_name
        ORDER BY dt1.items
          FOR XML PATH('')), 1, LEN(','), '') AS items,
          STUFF((
       SELECT distinct ';'   dt1.place
         FROM deck_table dt1
        WHERE dt1.deck_name = dt.deck_name
        ORDER BY dt1.place
          FOR XML PATH('')), 1, LEN(','), '') AS place,
          STUFF((
       SELECT distinct ';'   dt1.username
         FROM deck_table dt1
        WHERE dt1.deck_name = dt.deck_name
        ORDER BY dt1.username
          FOR XML PATH('')), 1, LEN(','), '') AS username
from deck_table dt
group by deck_name