Combine multiple rows into distinct rows

Question

I have below case -

Id, Title, description, color, size, usages,  style
------------------------------------------------------------------
1 , Shoe,  sports shoe,  black, 10,    ,
1 , shoe,  sports shoe,       , 11,    ,
1 , shoe,  sports shoe,       , 12,    ,
1 , shoe,  sports shoe,       ,   ,  Sports,

This needs to be in below format

Id, Title, description, color, size, usages,     style
----------------------------------------------------------------
1 , Shoe,  sports shoe,  black, 10,    sports,
1 , Shoe,  sports shoe,  black, 11,    sports,
1 , Shoe,  sports shoe,  black, 12,    sports,

Could you please help me to create query of the same, basically, I am here merging all rows in distinct rows.

Answer 1

0

This will work:

select 
ID, Title, Description, 
(select top 1 colour FROM @ShoeStuff sscolour WHERE sscolour.ID = ss.id AND sscolour.colour IS NOT NULL AND sscolour.colour <> '') AS Colour,
size,
(select top 1 usages FROM @ShoeStuff ssusages WHERE ssusages.ID = ss.ID AND ssusages.usages IS NOT NULL AND ssusages.usages <> '') AS Usages,
style
FROM @ShoeStuff ss
WHERE size IS NOT NULL

However, it's not elegant. And it might not exactly match your business rules. (replace @shoestuff with whatever the name of your table is...)

answered Sep 19 '12 at 08:45 AM

ThomasRushton ♦
29.4k ● 6 ● 9 ● 36

(comments are locked)

Combine multiple rows into distinct rows

Follow this question

Related Questions