complex pivot - 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)

complex pivot

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-05-12 : 10:47:19



Consumer_ID                   Exam 1 / 2                                               Result
A                                        1                                                2
A                                        2                                                3
B                                        1                                                1  
B                                        2                                                2
C                                        1                                                2
C                                        2                                                3
D                                        1                                                1
D                                        2                                                2
E                                        1                                                4
E                                        2                                                5

I would like to put the sum of count for exam 1 result to Y(row) and exam 2 result to X (column). The exam result will have values from 0 to 5 ( fixed)

For example, Consumer A has 2 as exam 1 result and 3 as exam 2 result.. And consumer C has the same result .. So I put 2 for 2 (Y) and 3 (X) pivot..

The output should look like below.. How can I do this in T-SQL 2005?


          0 1 2 3 4 5

0             
1             2
2               2
3
4                   1 
5

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-12 : 10:50:47

See this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96796

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-12 : 11:11:40

[code]SELECT p.Result,
p.[0],
p.[1],
p.[2],
p.[3],
p.[4],
p.[5]
FROM (
SELECT MAX(CASE WHEN Exam = 1 THEN Result ELSE 0 END) AS Result,
MAX(CASE WHEN Exam = 2 THEN Result ELSE 0 END) AS Ex2
FROM @Sample
GROUP BY ConsumerID
) AS s
PIVOT (
COUNT(Ex2)
FOR Ex2 IN ([0], [1], [2], [3], [4], [5])
) AS p[/code]

E 12°55'05.25"
N 56°04'39.16"

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-05-12 : 12:04:39

Thank you Peso.. I will try it and let you know

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-05-13 : 00:23:55

quote:

Originally posted by Peso

SELECT		p.Result,
		p.[0],
		p.[1],
		p.[2],
		p.[3],
		p.[4],
		p.[5]
FROM		(
			SELECT		MAX(CASE WHEN Exam = 1 THEN Result ELSE 0 END) AS Result,
					MAX(CASE WHEN Exam = 2 THEN Result ELSE 0 END) AS Ex2
			FROM		@Sample
			GROUP BY	ConsumerID
		) AS s
PIVOT		(
			COUNT(Ex2)
			FOR Ex2 IN ([0], [1], [2], [3], [4], [5])
		) AS p

E 12°55'05.25"
N 56°04'39.16"

I've tried your code.. only problem is i need to create a fixed rows..


        0      1        2       3       4       5 
0       0       0       0       0       0       0
1       0       0       0       0       0       0
2	0	0	0	1	2	0
3	0	0	0	1	0	0
4       0       0       0       0       0       0 
5       0       0        0      0       0       0

How can I create the same fixed numbers for rows? thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 02:38:45

[code]DECLARE @Sample TABLE (ConsumerID CHAR(1), Exam INT, Result INT)

INSERT @Sample
SELECT 'A', 1, 2 UNION ALL
SELECT 'A', 2, 3 UNION ALL
SELECT 'B', 1, 1 UNION ALL
SELECT 'B', 2, 2 UNION ALL
SELECT 'C', 1, 2 UNION ALL
SELECT 'C', 2, 3 UNION ALL
SELECT 'D', 1, 1 UNION ALL
SELECT 'D', 2, 2 UNION ALL
SELECT 'E', 1, 4 UNION ALL
SELECT 'E', 2, 5

SELECT p.Result,
p.[0],
p.[1],
p.[2],
p.[3],
p.[4],
p.[5]
FROM (
SELECT MAX(CASE WHEN Exam = 1 THEN Result ELSE 0 END) AS Result,
MAX(CASE WHEN Exam = 2 THEN Result ELSE 0 END) AS Ex2
FROM @Sample
GROUP BY ConsumerID

UNION ALL

SELECT Number,
6
FROM master..spt_values
WHERE Type = 'p'
AND Number < 6
) AS s
PIVOT (
COUNT(Ex2)
FOR Ex2 IN ([0], [1], [2], [3], [4], [5])
) AS p[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources