SQL - multiple lines into one
First of all, I looked over this one and my question is different - albeit slightly. Also I tried the approach and tried to change it to work for me, but not cubes.
Question:
From the result of multiple queries, I get the following result:
ID | NAME | DESCID | TALL | GOODLOOKING | FAT
_______________________________________________________________
1 | John | 1 | '1.8m' | Null | Null
1 | John | 2 | Null | 'Yes' | Null
1 | John | 3 | Null | Null | '84kg'
1 | John | 4 | Null | Null | '85kg'
Note. Just some bogus BTW data.
I need the output:
ID | NAME | TALL | GOODLOOKING | FAT
__________________________________________________________
1 | John | '1.8m' | 'Yes' | '84kg|85kg'
If this is not possible, I would appreciate it, so feel free to tell me.
Any SQL Legend hints?
+3
source to share
1 answer
There is nothing like this in SQL Server Group_Concat
that you can use directly. You can use FOR XML
in a correlated query
SELECT ID,NAME,
STUFF((SELECT '|'+TALL FROM Tbl1 t2 WHERE t2.ID = t1.ID AND t2.NAME = t1.NAME ORDER BY t2.descid FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') as tall,
STUFF((SELECT '|'+GOODLOOKING FROM Tbl1 t2 WHERE t2.ID = t1.ID AND t2.NAME = t1.NAME ORDER BY t2.descid FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') as GOODLOOKING,
STUFF((SELECT '|'+FAT FROM Tbl1 t2 WHERE t2.ID = t1.ID AND t2.NAME = t1.NAME ORDER BY t2.descid FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') as FAT
FROM Tbl1 t1
GROUP BY ID,NAME
+3
source to share