Skip to content
Advertisement

Concatenate from rows in SQL server

I want to concatenate from multiple rows

Table:

|id      |Attribute   |Value    |
|--------|------------|---------|
|101     |Manager     |Rudolf   |
|101     |Account     |456      |
|101     |Code        |B        |
|102     |Manager     |Anna     |
|102     |Cardno      |123      |
|102     |Code        |B        |
|102     |Code        |C        |

The result I’m looking for is:

|id      |Manager|Account|Cardno|Code      |
|--------|-------|-------|------|----------|
|101     |Rudolf |456    |      |B         |
|102     |Anna   |       |123   |B,C       |

I have the following code from a related question:

select
  p.*,
  a.value as Manager,
  b.value as Account,
  c.value as Cardno
from table1 p
left join table2 a on a.id = p.id and a.attribute = 'Manager'
left join table2 b on b.id = p.id and b.attribute = 'Account'
left join table2 c on c.id = p.id and b.attribute = 'Cardno'

However, it fails for the Code attribute with ID# 102, where both B and C values are present.

How can I update this to include both of those values in the same result?

Advertisement

Answer

If you are using SQL SERVER 2017 or above then string_agg() with PIVOT() will be easy to use but much faster in performance solution (Query#1).

If you are using older version of SQL Server then go for Query#2 with STUFF() and XML PATH FOR() for concatenating value along with PIVOT()

Schema:

 create table table1 (id int, Attribute varchar(50) , Value  varchar(50));
 
 insert into table1 values(101     ,'Manager'     ,'Rudolf');   
 insert into table1 values(101     ,'Account'     ,'456');   
 insert into table1 values(101     ,'Code'        ,'B');
 insert into table1 values(102     ,'Manager'     ,'Anna');
 insert into table1 values(102     ,'Cardno'      ,'123');
 insert into table1 values(102     ,'Code'        ,'B');
 insert into table1 values(102     ,'Code'        ,'C');
 GO

Query#1 PIVOT() with STRING_AGG():

 select *  
 from
 (
     select t1.id,t1.attribute,
     string_agg(value,',')  AS value
     from table1 t1
     group by t1.id,t1.attribute
 ) d
 pivot
 (
   max(value)
   for attribute in (manager,account,cardno,code)
 ) piv

Output:

id manager account cardno code
101 Rudolf 456 <emnull</em B
102 Anna <emnull</em 123 B,C

Query#2 PIVOT() WITH STUFF() AND XML PATH FOR():

 select *  
 from
 (
     select distinct t1.id,t1.attribute,
   STUFF(
          (SELECT ', ' + convert(varchar(10), t2.value, 120)
           FROM table1 t2
           where t1.id = t2.id and t1.attribute=t2.attribute
           FOR XML PATH (''))
           , 1, 1, '')  AS value
 from table1 t1
 ) d
 pivot
 (
   max(value)
   for attribute in (manager,account,cardno,code)
 ) piv

Output:

id manager account cardno code
101 Rudolf 456 <emnull</em B
102 Anna <emnull</em 123 B, C

db<fiddle here

Advertisement