question

abhips avatar image
abhips asked

Combine multiple rows into distinct rows

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.
sqlquery
5 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Yes, but how do you want it to be treated when doing the rollup / summary / combination?
1 Like 1 ·
Phil Factor avatar image Phil Factor commented ·
in this list, what is implied by leaving a column blank? It isn't obvious from the example, I'm afraid. What would one do, for example if there was a line like this? 1 , shoe, sports shoe, brown, , Sports,
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
...and what about the "style" column?
0 Likes 0 ·
abhips avatar image abhips commented ·
that column can have some value, in this case i kept it blank.
0 Likes 0 ·
abhips avatar image abhips commented ·
I did not get your question. but let me tell this - in this case we can ignore that column.
0 Likes 0 ·

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
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...)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.