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

abhips gravatar 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

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x716
x369

asked: Sep 14, 2012 at 06:14 PM

Seen: 937 times

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