x

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.

more ▼

asked Sep 14, 2012 at 06:14 PM in Default

avatar image

abhips
0 1 1 2

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,
Sep 17, 2012 at 11:26 AM Phil Factor

...and what about the "style" column?

Sep 17, 2012 at 11:34 AM ThomasRushton ♦♦

that column can have some value, in this case i kept it blank.

Sep 17, 2012 at 04:06 PM abhips

Yes, but how do you want it to be treated when doing the rollup / summary / combination?

Sep 17, 2012 at 04:11 PM ThomasRushton ♦♦

I did not get your question. but let me tell this - in this case we can ignore that column.

Sep 19, 2012 at 06:11 AM abhips
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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...)

more ▼

answered Sep 19, 2012 at 08:45 AM

avatar image

ThomasRushton ♦♦
39.9k 20 49 52

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x994
x426

asked: Sep 14, 2012 at 06:14 PM

Seen: 1217 times

Last Updated: Sep 19, 2012 at 08:46 AM

Copyright 2016 Redgate Software. Privacy Policy