Solved

SQL PIVOT

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

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