How do I transpose columns to rows?

Question

Post reply

How do I transpose columns to rows?

rmdyyc

SSC Veteran

Points: 229
More actions
October 29, 2015 at 10:55 am

#309245

Good day. I am struggling with doing what should be a fairly simply transpose of columns to rows. I have found some examples but nothing I could get working. I am stuck and would appreciate help you have time.
I have data in the form of:
Brand, Model, Color1, Color2, Color3, Color4, Color5, Color6
Honda, Accord, Red, Grey, Black, White,,,
Toyota, Corolla, White, Black,,,,,
The output should look like:
Brand, Model, Color
Honda, Accord, Red
Honda, Accord, Grey
Honda, Accord, Black
Honda, Accord, White
Toyota, Corolla, White
Toyota, Corolla, Black
Thank you for any assistance in advance. It is greatly appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Have you tried this?

http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

This is your first post here, so you might not be used to it. In SQL forums, it's a good practice to post your sample data in a way that we can copy, paste and execute to create it in our environments. This way we can concentrate in the problem instead of losing time generating it.

Here's a full example with sample data as you should have post it.

CREATE TABLE #Sample(

Brand varchar(20),

Model varchar(20),

Color1 varchar(20),

Color2 varchar(20),

Color3 varchar(20),

Color4 varchar(20),

Color5 varchar(20),

Color6 varchar(20));

INSERT INTO #Sample

VALUES( 'Honda', 'Accord', 'Red', 'Grey', 'Black', 'White','',''),

( 'Toyota', 'Corolla', 'White', 'Black','','','','');

SELECT Brand, Model, Color

FROM #Sample s

CROSS APPLY (VALUES(Color1),(Color2),(Color3),(Color4),(Color5),(Color6)) u(Color)

WHERE Color <> '';

SELECT Brand, Model, Color

FROM (

SELECT Brand, Model, Color1, Color2, Color3, Color4, Color5, Color6

FROM #Sample) p

UNPIVOT

(Color FOR ColorID IN (Color1, Color2, Color3, Color4, Color5, Color6)) as unpvt

WHERE Color <> ''

GO

DROP TABLE #Sample;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

rmdyyc SSC Veteran Points: 229 More actions · Answer 3

Hi Luis,

This is long overdue, but thank you for your solution. It worked very well and I hope others can learn from it as well. I am extremely grateful for your assistance.