Solved

SQL PIVOT

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

705 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now