Pivot based off two columns

Question

Post reply

Pivot based off two columns

John215

SSC Eights!

Points: 871
More actions
February 17, 2016 at 7:30 am

#308057

Morning,
I have a table that looks like this:
INSERT INTO TEST (COL1,COL2,COL3,ITEM1,DESC1,ITEM2,DESC2,ITEM3,DESC3)
VALUES
('AAA','BBB','CCC','DOG','BROWN','CAT','RED','RAT','GREEN')
,('A','B','C','DOG','ORANGE','DOG','BLUE','DOG','BLUE')
,('BBB','BBB','BBB','RAT','GREY','DOG','RED','DOG','RED')
,('BBB','BBB','BBB','CAT','GREEN','CAT','NULL','NULL','NULL')
,('F','F','F','DOG','BLACK','DOG','BLUE','DOG','BLUE')
,('F','F','F','DOG','BLUE','NULL','NULL','NULL','NULL')
I need it pivot (ITEM1, DESC1), (ITEM2, DESC2), (ITEM3,DESC3) based off of COL1, COL2, COL3. If COL1, COL2, COL3 is repeated I still want all all values.
The pivot should look like this
COL1COL2COL3COL4COL5
AAABBBCCCDOGBROWN
AAABBBCCCCATRED
AAABBBCCCRATGREEN
ABCDOGORANGE
ABCDOGBLUE
ABCDOGBLUE
BBBBBBBBBRATGREY
BBBBBBBBBDOGRED
BBBBBBBBBDOGRED
BBBBBBBBBCATGREEN
BBBBBBBBBCATNULL
FFFDOGBLACK
FFFDOGBLUE
FFFDOGBLUE
FFFDOGBLUE
I was trying to use the website below but ran into some issues. Any help would be great.
http://mangalpardeshi.blogspot.com/2009/04/unpivot-multiple-columns.html

Viewing 6 posts - 1 through 5 (of 5 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

SELECT COL1, COL2, COL3, x.*

FROM #TEST

CROSS APPLY (VALUES (ITEM1, DESC1),(ITEM2, DESC2),(ITEM3, DESC3)) x (COL4, COL5)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

John215 SSC Eights! Points: 871 More actions · Answer 2

John215

SSC Eights!

Points: 871

February 17, 2016 at 1:15 pm

#1859362

I am speechless. THANK YOU SOOOOO MUCH.

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

Find more information in this article:

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

imba (2/17/2016)
I am speechless. THANK YOU SOOOOO MUCH.

You're welcome, thanks for the feedback.

Be sure to read Dwain's excellent article linked by Luis.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

patilar SSC Veteran Points: 218 More actions · Answer 5

patilar

SSC Veteran

Points: 218

February 18, 2016 at 2:23 am

#1859479

Learned something new today 🙂