CODEWITHSUNDEEP
sqlsql-server
Terrance00
Terrance00

Reputation: 1678

SQL - Multiple Rows Into One

First of all, I've looked at this, and my question is different - albeit slightly. Also I have tried the approach and tried to modify it to work for me, but no dice.

Question:

From a result of a couple of 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 dummy data BTW.

I need the output to be the following:

ID   |  NAME  |   TALL   |   GOODLOOKING   |       FAT
__________________________________________________________
1    |  John  |  '1.8m'  |       'Yes'     |   '84kg|85kg'

If this is not possible I would appreciate knowing that, so feel free to tell me.

Any help SQL legends?

Upvotes: 3

Views: 80

Answers (1)

ughai
ughai

Reputation: 9880

There is nothing like Group_Concat in SQL Server which can be used 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

SQL Fiddle

Upvotes: 3

Related Questions