PIVOT?UNPIVOT? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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?UNPIVOT?

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-01 : 01:42:44

[code]CREATE TABLE #category(
cat_code varchar(30),
total_rm decimal(21,8),
total_cn decimal(21,8),
qty decimal(21,8),
date datetime)

INSERT #category
SELECT 'C1', 5, 85, 32,'2007-05-01 00:00:00.000' UNION ALL
SELECT 'C1', 4, 5340, 3,'2007-07-01 00:00:00.000' UNION ALL
SELECT 'C1', 83, 100, 32,'2007-04-01 00:00:00.000' UNION ALL
SELECT 'C1', 75, 1380, 53,'2007-04-01 00:00:00.000' UNION ALL
SELECT 'C1', 43, 865, 42323,'2007-04-01 00:00:00.000' UNION ALL
SELECT 'C1', 23, 38, 83,'2007-12-01 00:00:00.000' UNION ALL
SELECT 'C1', 12, 282, 33,'2007-10-01 00:00:00.000' UNION ALL
SELECT 'C3', 75, 60, 8,'2007-08-01 00:00:00.000' UNION ALL
SELECT 'C3', 23, 668, 4,'2007-08-01 00:00:00.000'

SELECT cat_code, [1],[2],[3],[4],[5],[6],[7],

,[9],[10],[11],[12]
FROM (SELECT total_rm, cat_code, DATEDIFF(month, '20070101', date) + 1 as date
FROM #category)up
PIVOT (
sum(total_rm) FOR date IN ([1],[2],[3],[4],[5],[6],[7],

,[9],[10],[11],[12])) as pvt
ORDER BY cat_code

DROP TABLE #category
result
cat_code 1 2 3 4 5 6 7 8 9 10 11 12
------------------------------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
C1 NULL NULL NULL 201.00000000 5.00000000 NULL 4.00000000 NULL NULL 12.00000000 NULL 23.00000000
C3 NULL NULL NULL NULL NULL NULL NULL 98.00000000 NULL NULL NULL NULL

Expected result...something like
cat_code|type|1|2|3|4|5|6|7|8|9|10|11|12
C1|total_rm|NULL|NULL|NULL|201.00000000|5.00000000|NULL|4.00000000|NULL|NULL|12.00000000|NULL|23.00000000
C1|total_cn|NULL|NULL|NULL|2345.00000000|85.00000000|NULL|5340.00000000|NULL|NULL|282.00000000|NULL|38.00000000
C1|qty|NULL|NULL|NULL|42408.00000000|32.00000000|NULL|3.00000000|NULL|NULL|33.00000000|NULL|83.00000000
C3|total_rm|NULL|NULL|NULL|NULL|NULL|NULL|NULL|98.00000000|NULL|NULL|NULL|NULL
C3|total_cn|NULL|NULL|NULL|NULL|NULL|NULL|NULL|728.00000000|NULL|NULL|NULL|NULL
C3|qty|NULL|NULL|NULL|NULL|NULL|NULL|NULL|12.00000000|NULL|NULL|NULL|NULL[/code]

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 01:56:42

try


SELECT	cat_code, type, [1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12]
FROM	
(
	SELECT	type = 'total_rm', total = total_rm, cat_code, DATEDIFF(month, '20070101', date) + 1 as date
	FROM	#category

	union all

	SELECT	type = 'total_cn', total = total_cn, cat_code, DATEDIFF(month, '20070101', date) + 1 as date
	FROM	#category

	union all
	SELECT	type = 'qty', total = qty, cat_code, DATEDIFF(month, '20070101', date) + 1 as date
	FROM	#category
)up
PIVOT	
(
	sum(total) FOR date IN ([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12])
) as pvt
ORDER BY cat_code

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-01 : 01:58:46

wow thx mr.tan ^^ i still trying put all column into pivot haha...but i think yours rock

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 02:04:16

you are welcome

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-01 : 02:05:46

erm...1 more question....how to add a sum total after [12]
something like cube?

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 02:08:08

[code]SELECT . . .[11], [12], [1] + [2] + . . . + [12] as [Total][/code]

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-01 : 02:09:01

LOL!!!! like that also can!!!!

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 02:23:36

Tentu Boleh

[spoiler]Affirmative[/spoiler]

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-01 : 02:29:21

=.= You know malays!!!

AMITOFO+AMEN+YA ALLAH Hope the query works

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources