SQL - Multiple Rows Into One

Question

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?

so only concatenation in fat column? what if Goodlooking had 2 not null distinct values? — ughai
– ughai, Commented Jun 17, 2015 at 10:43
Concatenation in any of the columns following descid. Thanks for asking — Terrance00
– Terrance00, Commented Jun 17, 2015 at 10:45

Andriy M · Accepted Answer · 2015-06-17 15:14:19Z

3

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

edited Jun 17, 2015 at 15:14

Andriy M

78k18 gold badges100 silver badges157 bronze badges

answered Jun 17, 2015 at 10:54

ughai

9,9004 gold badges33 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Terrance00 Over a year ago

Looks good. I've upvoted for effort, will mark as answer after test. Thanks

Terrance00 Over a year ago

Okay well This works. The solution however is too slow. Is that just a product of this type of work? Because this is slower than brunt looping in c# - considerably.

ughai Over a year ago

Yes, This would be slow as sql needs to read the table 4 times.

Andriy M Over a year ago

@Terrance00: Since you are saying that the non-aggregated row set is the result of a couple of queries, you might try and see if the technique suggested here can be combined with any of those queries – who knows, it might perform better when applied to original datasets.

Collectives™ on Stack Overflow

SQL - Multiple Rows Into One

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related