Solved

SQL PIVOT

Posted on 2013-02-04
2
493 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 500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

724 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