Converting Rows into Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Converting Rows into Columns

Author

Topic

mynameisfelix
Starting Member

7 Posts

Posted - 2011-04-26 : 15:34:15

Hi,

I have a table structure like this

CustomerID LabelID LabelValue

100 CustomerCode ABC
100 CustomerPin XYZ
100 CustomerPass PQR
200 CustomerCode IOP
200 CustomerPin AIT
200 CustomerPass PPP

and I want the results to look like this. Can some one please help me with the query for this?

CustomerID CustomerCode CustomerPin CustomerPass
100 ABC XYZ PQR
200 IOP AIT PPP

Thanks is Advance

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-26 : 15:38:28

[code]DECLARE @t TABLE(CustomerID INT, LabelID VARCHAR(20), LabelValue VARCHAR(10))
INSERT @t SELECT
100,'CustomerCode','ABC' UNION ALL SELECT
100,'CustomerPin','XYZ' UNION ALL SELECT
100,'CustomerPass','PQR' UNION ALL SELECT
200,'CustomerCode','IOP' UNION ALL SELECT
200,'CustomerPin','AIT' UNION ALL SELECT
200,'CustomerPass','PPP'

SELECT CustomerID, CustomerCode, CustomerPin, CustomerPass
FROM @t
PIVOT(MAX(LabelValue) FOR LabelID IN(CustomerCode, CustomerPin, CustomerPass)) b[/code]

X002548
Not Just a Number

15586 Posts

Posted - 2011-04-26 : 16:17:06

Say thank you...when was that introduced? 2k5? or 2k8?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mynameisfelix
Starting Member

7 Posts

Posted - 2011-04-27 : 11:58:38

Thank you, that perfectly solved my problem. What if there is a primary key column in the table like this. How would the pivot query look like in this case?

ID CustomerID LabelID LabelValue

1 100 CustomerCode ABC
2 100 CustomerPin XYZ
3 100 CustomerPass PQR
4 200 CustomerCode IOP
5 200 CustomerPin AIT
6 200 CustomerPass PPP

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-27 : 12:04:02

If you include it in the SELECT clause then you won't get any pivoting, there's an implicit GROUP BY on unpivoted columns. You'll basically get the regular table back, and only one of the pivoted columns will contain data. Try it and you'll see what happens.

mynameisfelix
Starting Member

7 Posts

Posted - 2011-04-27 : 12:15:15

Thanks for the reply. This is what happened and this is the query that I am using now

DECLARE @t TABLE(ID int,CustomerID INT, LabelID VARCHAR(20), LabelValue VARCHAR(10))
INSERT @t SELECT
1, 100,'CustomerCode','ABC' UNION ALL SELECT
2, 100,'CustomerPin','XYZ' UNION ALL SELECT
3, 100,'CustomerPass','PQR' UNION ALL SELECT
4, 200,'CustomerCode','IOP' UNION ALL SELECT
5, 200,'CustomerPin','AIT' UNION ALL SELECT
6, 200,'CustomerPass','PPP'

SELECT CustomerID, CustomerCode, CustomerPin, CustomerPass
FROM @t
PIVOT(MAX(LabelValue) FOR LabelID IN(CustomerCode, CustomerPin, CustomerPass)) b

I am not using ID column in the select clause but these are the results that I am getting back now

CustomerID CustomerCode CustomerPin CustomerPass
----------- ------------ ----------- ------------
100 ABC NULL NULL
100 NULL XYZ NULL
100 NULL NULL PQR
200 IOP NULL NULL
200 NULL AIT NULL
200 NULL NULL PPP

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-27 : 12:18:23

[code]SELECT CustomerID, CustomerCode, CustomerPin, CustomerPass
FROM (SELECT CustomerID, LabelID, LabelValue FROM @t) a
PIVOT(MAX(LabelValue) FOR LabelID IN(CustomerCode, CustomerPin, CustomerPass)) b[/code]

mynameisfelix
Starting Member

7 Posts

Posted - 2011-04-27 : 12:22:01

That perfectly worked. Thank you so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources