?
Solved

SQL PIVOT

Posted on 2013-02-04
2
Medium Priority
?
494 Views
Last Modified: 2013-02-04
Say I have query that produces these results:

Current results
But I want to pivot the query to produce these results:

Desired results
-------------------------------------------------------------------------------------------------------------

CREATE TABLE tempdb.dbo.mh_pivot_candidate (
MyThingId INT NOT NULL,
Color1 VARCHAR(25) NULL,
Color2 VARCHAR(25) NULL,
Color3 VARCHAR(25) NULL,
Color4 VARCHAR(25) NULL,
Color5 VARCHAR(25) NULL
)

INSERT tempdb.dbo.mh_pivot_candidate VALUES (10, 'RED', 'WHITE', 'BLUE', 'GREEN', 'ORANGE')
INSERT tempdb.dbo.mh_pivot_candidate VALUES (20, 'WHITE', 'BLACK', 'GRAY', 'PURPLE', 'YELLOW')
INSERT tempdb.dbo.mh_pivot_candidate VALUES (30, 'RED', 'WHITE', 'BLUE', 'YELLOW', 'GRAY')
INSERT tempdb.dbo.mh_pivot_candidate VALUES (40, 'RED', 'WHITE', 'BLUE', NULL, NULL)
INSERT tempdb.dbo.mh_pivot_candidate VALUES (50, 'MAGENTA', NULL, NULL, NULL, NULL)

SELECT * FROM tempdb.dbo.mh_pivot_candidate
0
Comment
Question by:MariaHalt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 38851893
you can use sql servers unpivot extension to sql

like this

select mythingid,color
from dbo.mh_pivot_candidate as a
unpivot (color for attribute in (color1,color2,color3,color4,color5) ) as unpvt
order by mythingid,attribute

Open in new window




your requirement is actually to unpivot your data from columns into rows ,
pivot means putting rows into columns...

there are several very good articles written by ee members on the usage of the sql server pivot/unpivot extensions   under the sql server 2005/2008 topic areas...
0
 

Author Closing Comment

by:MariaHalt
ID: 38851934
Worked perfectly.  Thanks for the explanation too.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question