PIVOT operator - 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 operator

Author

Topic

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-27 : 02:35:51

Does anyone knows the PIVOT operator able to support 2 aggregate ? BOL does not seems to say so.

I have at least 3 column need to aggregate it. I can work around it by joining it in derived table with each derived table pivoting one column. But it will means i will be reading from the table more than once. Furthermore the codes will get more complicated.

Looks like I have to use the good old "case when" method

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2007-10-27 : 03:47:24

Some sample data, and the CASE WHEN stuff to show expected results would be good. I would expect it may be a lengthy, and worthy, thread of experimentation.

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-27 : 04:00:54

Yes Sir ! coming right up

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-27 : 04:20:07

the SQL 2000 method is what i have been doing all my t-sql life. I was hoping with the new PIVOT operator in SQL 2005, i can simplify my query. However, in most of the time, i will have more than 1 aggregate value. In SQL 2000, it can simply be done with additional line of case . . when. in SQL 2005, the only work around i can come up with is to use derived table.


-- Sample Data
DECLARE @sample TABLE
(
	cola	int,
	colb	int,
	val	int
)

INSERT INTO @sample
SELECT	10, 1, 100	UNION ALL
SELECT	10, 2, 110	UNION ALL
SELECT	10, 3, 110	UNION ALL
SELECT	10, 3, 120	UNION ALL
SELECT	20, 1, 200	UNION ALL
SELECT	20, 3, 210	UNION ALL
SELECT	30, 2, 310	UNION ALL
SELECT	30, 3, 320

-- SQL 2000 method
SELECT	cola,
	[sum 1] = SUM(CASE WHEN colb = 1 THEN val END),
	[sum 2] = SUM(CASE WHEN colb = 2 THEN val END),
	[sum 3] = SUM(CASE WHEN colb = 3 THEN val END),
	[cnt 1] = COUNT(CASE WHEN colb = 1 THEN val END),
	[cnt 2] = COUNT(CASE WHEN colb = 2 THEN val END),
	[cnt 3] = COUNT(CASE WHEN colb = 3 THEN val END)
FROM	@sample
GROUP BY cola

-- SQL 2005 PIVOT operator
SELECT	s.cola, 
	s.[1] AS [sum 1], s.[2] AS [sum 2], s.[3] AS [sum 3], 
	c.[1] AS [cnt 1], c.[2] AS [cnt 2], c.[3] AS [cnt 3]
FROM
(
	SELECT	cola, [1], [2], [3]
	FROM
	(
		SELECT	cola, colb, val
		FROM	@sample
	) a
	pivot
	(       -- sum()
		SUM(val)
		FOR colb IN ([1], [2], [3])
	) p
) s
INNER JOIN
(
	SELECT	cola, [1], [2], [3]
	FROM
	(
		SELECT	cola, colb, val
		FROM	@sample
	) a
	pivot
	(       -- count()
		COUNT(val)
		FOR colb IN ([1], [2], [3])
	) p
) c	
ON	s.cola	= c.cola

/* RESULT of both query :
cola        sum 1       sum 2       sum 3       cnt 1       cnt 2       cnt 3       
----------- ----------- ----------- ----------- ----------- ----------- ----------- 
         10         100         110         230           1           1           2 
         20         200 NULL                210           1           0           1 
         30 NULL                310         320           0           1           1 

(3 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources