SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 PIVOT multiple columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ivl
Starting Member

2 Posts

Posted - 10/21/2008 :  05:44:08  Show Profile  Reply with Quote
Hi folks,

Say I have the following table:

DECLARE	@Sample TABLE (Code INT, Row TINYINT, Mail VARCHAR(200))

INSERT INTO @Sample
SELECT 1111,	1,	'abc@yahoo.com'
UNION ALL SELECT 1111,	2,	'xyz@yahoo.com'
UNION ALL SELECT 1111,	3,	NULL
UNION ALL SELECT 2222,	1,	'abc2@yahoo.com'
UNION ALL SELECT 2222,	2,	NULL
UNION ALL SELECT 2222,	3,	'xyz2@yahoo.com'


and I want to get it into the format:
DECLARE	@Sample2 TABLE (Code INT, Mail1 VARCHAR(200),  Mail2 VARCHAR(200), Mail3 VARCHAR(200))


I would use the following query:
SELECT Code, [1] Mail1, [2] Mail2, [3] Mail3
FROM ( SELECT Code, Row, Mail FROM @Sample ) s
PIVOT (
	MIN(Mail)
	FOR Row IN ([1], [2], [3])
) AS p


Now what if in the initial @Sample table looks like this (an extra phone column):
DECLARE	@Sample TABLE (Code INT, Row TINYINT, Mail VARCHAR(200), Phone VARCHAR(200))

INSERT INTO @Sample
SELECT 1111,	1,	'abc@yahoo.com', '123456'
UNION ALL SELECT 1111,	2,	'xyz@yahoo.com', '234567'
UNION ALL SELECT 1111,	3,	NULL, NULL
UNION ALL SELECT 2222,	1,	'abc2@yahoo.com', '345678'
UNION ALL SELECT 2222,	2,	NULL, NULL
UNION ALL SELECT 2222,	3,	'xyz2@yahoo.com', '456789'


and I want to get the data into the following format:
DECLARE	@Sample2 TABLE (Code INT, Mail1 VARCHAR(200),  Mail2 VARCHAR(200), Mail3 VARCHAR(200), Phone1 VARCHAR(200), Phone2 VARCHAR(200), Phone3 VARCHAR(200))


the only query that I found that works is the following:
SELECT a.Code, a.[1] Mail1, a.[2] Mail2, a.[3] Mail3, b.[1] Phone1, b.[2] Phone2, b.[3] Phone3  FROM (
	SELECT Code, [1], [2], [3]
	FROM ( SELECT Code, Row, Mail FROM @Sample ) s
	PIVOT (
		MIN(Mail)
		FOR Row IN ([1], [2], [3])
	) AS p
) AS a
INNER JOIN (
	SELECT Code, [1], [2], [3]
	FROM ( SELECT Code, Row, Phone FROM @Sample ) s
	PIVOT (
		MIN(Phone)
		FOR Row IN ([1], [2], [3])
	) AS p
) AS b
ON a.Code = b.Code


So for each column that I need to pivot (in the last example, two columns), I need a different select, PIVOT and join.
My real world scenario requires about 8 columns to be pivoted (is there such a word? :) )
So I would need 8 different selects, PIVOTs and seven joins.

Is there another, simpler and more efficient way to do this?

I'm using SQL Server 2005.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 10/21/2008 :  06:17:14  Show Profile  Reply with Quote
try with CASE

SELECT	Code
		, MAX(CASE WHEN Row = 1 THEN Mail END ) AS 'Mail1'
		, MAX(CASE WHEN Row = 2 THEN Mail END ) AS 'Mail2'
		, MAX(CASE WHEN Row = 3 THEN Mail END ) AS 'Mail3'
		, MAX(CASE WHEN Row = 1 THEN Phone END ) AS 'Phone1'
		, MAX(CASE WHEN Row = 2 THEN Phone END ) AS 'Phone2'
		, MAX(CASE WHEN Row = 3 THEN Phone END ) AS 'Phone3'
FROM @Sample
GROUP BY Code
Go to Top of Page

ivl
Starting Member

2 Posts

Posted - 10/21/2008 :  06:52:24  Show Profile  Reply with Quote
Many thanks, PeterNeo.

I actually tried CASE initially, but the MAX aggregation function for each CASE was missing from my query and as such I got incorrect results.

I rewrote my query using CASE and MAX, and observed the following:

  • Query took 11 seconds yielding 83385 rows using CASE.

  • Query took 15 seconds yielding 83385 rows using my original (large) PIVOT query with all the joins.


I used DBCC DROPCLEANBUFFERS for each test.

So I do get a speed improvement and the query is much simpler.

Thank you!
Go to Top of Page

ngodat
Starting Member

Vietnam
1 Posts

Posted - 03/28/2010 :  23:36:37  Show Profile  Reply with Quote
Many thanks, ivl.
Can You helf me please... I want to get the data into the following format:

DECLARE @Sample2 TABLE (Code INT, Mail1 VARCHAR(200), Phone1 VARCHAR(200), Mail2 VARCHAR(200), Phone2 VARCHAR(200), Mail3 VARCHAR(200), Phone3 VARCHAR(200))

Thank you very much...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/29/2010 :  00:30:49  Show Profile  Reply with Quote
quote:
Originally posted by ngodat

Many thanks, ivl.
Can You helf me please... I want to get the data into the following format:

DECLARE @Sample2 TABLE (Code INT, Mail1 VARCHAR(200), Phone1 VARCHAR(200), Mail2 VARCHAR(200), Phone2 VARCHAR(200), Mail3 VARCHAR(200), Phone3 VARCHAR(200))

Thank you very much...


Will it be always three values you want? also on what order you want to extract values in case you've more than three?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 07/27/2010 :  04:52:13  Show Profile  Reply with Quote
DECLARE @Sample TABLE (Code INT, Row TINYINT, Mail VARCHAR(200))

INSERT INTO @Sample
SELECT 1111, 1, 'abc@yahoo.com'
UNION ALL SELECT 1111, 2, 'xyz@yahoo.com'
UNION ALL SELECT 1111, 3, NULL
UNION ALL SELECT 2222, 1, 'abc2@yahoo.com'
UNION ALL SELECT 2222, 2, NULL
UNION ALL SELECT 2222, 3, 'xyz2@yahoo.com'


select S.Code,[Mail1]=S.Mail,[Mail2]=S1.Mail,[Mail3]=S2.Mail
from @Sample S
join
@Sample S1 on S.Row + 1 = S1.Row and S.Code = S1.Code
join
@Sample S2 on S.Row + 2 = S2.Row and S.Code = S2.Code

Iam a slow walker but i never walk back
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 07/28/2010 :  06:29:40  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Note sure if this helps
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000