Posted on 2013-02-04
Medium Priority
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 (
Color1 VARCHAR(25) NULL,
Color2 VARCHAR(25) NULL,
Color3 VARCHAR(25) NULL,
Color4 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
Question by:MariaHalt
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
LVL 50

Accepted Solution

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

Author Closing Comment

ID: 38851934
Worked perfectly.  Thanks for the explanation too.

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