PIVOT multiple 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)

PIVOT multiple columns

Author

Topic

ivl
Starting Member

2 Posts

Posted - 2008-10-21 : 05:44:08

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 - 2008-10-21 : 06:17:14

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

ivl
Starting Member

2 Posts

Posted - 2008-10-21 : 06:52:24

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!

ngodat
Starting Member

1 Post

Posted - 2010-03-28 : 23:36:37

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...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 00:30:49

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/

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-07-27 : 04:52:13

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-28 : 06:29:40

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources