How to concatenate multiple columns values into a single value per unique row

Question

I have a table with data of the duplicate result set, like the column names and the values which caused the duplicates.

ColName	ColValue	UniqueIDpercombination
KitiD	K89901	1
Kit	00900	1
KitiD	L7865	2
Kit	00400	2
UPC	345234	3
UPN	AVF	3

...... and so on.

I would like to combine the colname and colvalues into a single row per uniqueID as shown in below table. The column name values are not fixed.

ColName	ColValue	UniqueIDpercombination
KitiD - Kit	K89901 - 00900	1
KitiD - Kit	L7865 - 00400	2
UPC - UPN	345234 AVF	3

I tried using stuff with xml path, I did not get the desired output, below is the query which I tried and the output.

SELECT STUFF
(
    (
        SELECT '-' + s.Colname +','  
        FROM ##resultset s 
         --group by uniqueidpercombination, colname
         FOR XML PATH('')
    ),
     1, 1, '' 
) AS colname 

,STUFF
(
    (
        SELECT '/' + s.colvalue
        FROM ##resultsets
         --group by uniqueidpercombination, colvalue
         FOR XML PATH('')
    ),
     1, 1, ''
) AS colvale

ColName	ColValue
kitid-kit-kitid-kit	K89901-00900 -L7865-00400

Any suggestions on how to fix this?.

GRIV · Answer

Use a combination of STUFF and XML PATH then GROUP BY the UniqueIDpercombination column.

You were close with your query, however, you need to SELECT from your table first, then use a WHERE clause in your subqueries to essentially JOIN your outer query with your inner subqueries with a GROUP BY. That way you won't return the entire table in one single row.

It's also worth noting, I used 2 as the length for the STUFF function to account for the space in your ' - ' delimiter. You can play around with the lengths and delimiters that you need by changing the arguments passed into the STUFF function.

STUFF ( character_expression , start , length , replaceWith_expression )

Query:

SELECT 
      STUFF((SELECT ' - ' + ColName
         FROM sample_table b
         WHERE a.UniqueIDpercombination = b.UniqueIDpercombination
         FOR XML PATH('')), 1, 2, '') AS ColName,
      STUFF((SELECT ' - ' + ColValue
         FROM sample_table b
         WHERE a.UniqueIDpercombination = b.UniqueIDpercombination
         FOR XML PATH('')), 1, 2, '') AS ColValue,
     a.UniqueIDpercombination
FROM sample_table a
GROUP BY UniqueIDpercombination
ORDER BY UniqueIDpercombination

db<>fiddle here.

Bagus Ikhsan Bagaskara · Answer

create table #temp (
ColName varchar (10),
ColValue varchar (10),
UniqueIDpercombination int
)

insert into #temp values ('KitiD',  'K89901',   1)
insert into #temp values ('Kit',    '00900',    1        )
insert into #temp values ('KitiD',  'L7865',    2    )
insert into #temp values ('Kit',    '00400',    2        )
insert into #temp values ('UPC',    '345234',   3    )
insert into #temp values ('UPN',    'AVF',  3        )

select *,ROW_NUMBER() over(partition by uniqueIDpercombination order by uniqueIDpercombination) as rownum into #joinTable from #temp

step 1: if you want to use join table / single use


select distinct UniqueIDpercombination into #masterTable from #temp

select concat(b1.ColName,' - ',b2.ColName) ColName,CONCAT(b1.ColValue,' - ',b2.ColValue) ColValue,a.UniqueIDpercombination from #masterTable a
left join #joinTable b1 on a.UniqueIDpercombination = b1.UniqueIDpercombination and b1.rownum = 1
left join #joinTable b2 on a.UniqueIDpercombination = b2.UniqueIDpercombination and b2.rownum = 2

step 2: if you want to use loop / if the number of duplicate is not only 2 row


select cast(NULL as varchar) ColName, cast(NULL as varchar) ColValue,  UniqueIDpercombination into #masterTable1 from #temp
group by UniqueIDpercombination

declare @i int
set @i = 1

while @i <= (select max(rownum) from #joinTable)
begin
update a
set 
a.ColName = case when a.ColName is null then b.ColName else (case when b.ColName is null then a.ColName else concat(a.ColName,' - ',b.ColName)  end) end,
a.ColValue = case when a.ColValue is null then b.ColValue else (case when b.ColValue is null then a.ColValue else concat(a.ColValue,' - ',b.ColValue) end) end
from #masterTable1 a
left join #joinTable b on a.UniqueIDpercombination = b.UniqueIDpercombination and b.rownum = @i

set @i = @i +1
end

db <> fiddle https://dbfiddle.uk/CQ3RSYCQ

How to concatenate multiple columns values into a single value per unique row

2 Answers