Friday, August 2, 2013

Merge Multiple Rows in SQL Server

I was just going through a problem in database which was related to querying data from two tables but all the uncommon columns should be merged to a single row even comma separated.

Primary Table
ID
Value_1
Value_2
1
ABC
DEF
2
GEH
IJK


Foreign Table
ID
FK_ID
Value_1
1
1
LMK
2
1
IJKK
3
2
ZZZ
4
2
YYY

 Expected Result
PK.ID
FK_ID
PK.Value_1
FK_Value_1
PK.Value_2
1
1122,2122
ABC
LMK,IJKK
LKM
2
3122,4122
GEH
ZZZ,YYY
NOP

Query
Select PK.ID,PK.Value_1, PK.Value_2,
      (SELECT STUFF( (SELECT ', ' +  CONVERT(varchar(25), FK.ID, 1) 
            FROM FOREIGN_TABLE FK  WHERE FK.ID=PK.ID
                  FOR XML PATH ('')) , 1, 2, ''))   AS FK_ID ,
      (SELECT STUFF( (SELECT ', ' +  CONVERT(varchar(25), FK2.Value_1, 1)
             FROM FOREIGN_TABLE FK2 WHERE FK2.ID=PK.ID 
                  FOR XML PATH ('')) , 1, 2, ''))   AS FK_Value_1 
FROM PRIMARY_TABLE PK 

This query will be performance wise slow but, in SQL Server we don't have group concat like MySQL so, we will have to bear the pain.