MS SQL Server - How to pivot on two field

Author

How to pivot on two field

2006-02-28, 8:23 pm

I have a health check result table for students in a primary school.
The table is like below.
I use pivot to count the students count for every resultID:

Select GradeID, Gender ,[1] AS r1,[2] as r2 ,[3] as r3
FROM
(SELECT CheckID, GradeID, Gender, ResultID
FROM [Result]) p
PIVOT
(
COUNT (CheckID)
FOR ResultID IN
( [1], [2], [3])
) AS pvt
ORDER BY GradeID;

and get the pivot table:
----------------------Pivot on ResultID-----------------------
GradeID, Gender, R1, R2,R3
1 Female 1 3 0
1 Male 4 0 0
2 Male 3 1 0
3 Female 0 4 4
4 Male 0 0 4
5 Female 0 0 4
6 Male 3 1 0

It can only pivot on ResultID, but if I want to Pivot on Gender and
ResultID, and get the pivot table like:

GradeID, Male_R1, Male_R2,Male_R3,Fema
le_R1, Female_R2,Female_R3

How can I do?

Raw data:----------------------------------------
CheckID GradeID Gender ResultID
101 1 Male 1
102 2 Male 1
103 4 Male 3
104 3 Female 2
105 1 Female 2
106 3 Female 3
107 5 Female 3
108 6 Male 1
109 1 Male 1
110 2 Male 1
111 4 Male 3
112 3 Female 2

Tom Moreau

2006-02-28, 8:23 pm

Use two CTE's - one for the Male pivot and the other for the Female pivot.
Then do a join on GradeID between the two.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"ad" <flying@wfes.tcc.edu.tw> wrote in message
news:evBN5wxOGHA.3144@TK2MSFTNGP11.phx.gbl...
I have a health check result table for students in a primary school.
The table is like below.
I use pivot to count the students count for every resultID:

Select GradeID, Gender ,[1] AS r1,[2] as r2 ,[3] as r3
FROM
(SELECT CheckID, GradeID, Gender, ResultID
FROM [Result]) p
PIVOT
(
COUNT (CheckID)
FOR ResultID IN
( [1], [2], [3])
) AS pvt
ORDER BY GradeID;

and get the pivot table:
----------------------Pivot on ResultID-----------------------
GradeID, Gender, R1, R2,R3
1 Female 1 3 0
1 Male 4 0 0
2 Male 3 1 0
3 Female 0 4 4
4 Male 0 0 4
5 Female 0 0 4
6 Male 3 1 0

It can only pivot on ResultID, but if I want to Pivot on Gender and
ResultID, and get the pivot table like:

GradeID, Male_R1, Male_R2,Male_R3,Fema
le_R1, Female_R2,Female_R3

How can I do?

Raw data:----------------------------------------
CheckID GradeID Gender ResultID
101 1 Male 1
102 2 Male 1
103 4 Male 3
104 3 Female 2
105 1 Female 2
106 3 Female 3
107 5 Female 3
108 6 Male 1
109 1 Male 1
110 2 Male 1
111 4 Male 3
112 3 Female 2

2006-02-28, 8:23 pm

Thanks,
Could you give me an example?

"Tom Moreau" <tom@dont.spam.me.cips.ca>
???????:%23pzKKXyOGHA.2604@TK2MSFTNGP09.phx.gbl...
> Use two CTE's - one for the Male pivot and the other for the Female pivot.
> Then do a join on GradeID between the two.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "ad" <flying@wfes.tcc.edu.tw> wrote in message
> news:evBN5wxOGHA.3144@TK2MSFTNGP11.phx.gbl...
> I have a health check result table for students in a primary school.
> The table is like below.
> I use pivot to count the students count for every resultID:
>
>
> Select GradeID, Gender ,[1] AS r1,[2] as r2 ,[3] as r3
> FROM
> (SELECT CheckID, GradeID, Gender, ResultID
> FROM [Result]) p
> PIVOT
> (
> COUNT (CheckID)
> FOR ResultID IN
> ( [1], [2], [3])
> ) AS pvt
> ORDER BY GradeID;
>
> and get the pivot table:
> ----------------------Pivot on ResultID-----------------------
> GradeID, Gender, R1, R2,R3
> 1 Female 1 3 0
> 1 Male 4 0 0
> 2 Male 3 1 0
> 3 Female 0 4 4
> 4 Male 0 0 4
> 5 Female 0 0 4
> 6 Male 3 1 0
>
> It can only pivot on ResultID, but if I want to Pivot on Gender and
> ResultID, and get the pivot table like:
>
> GradeID, Male_R1, Male_R2,Male_R3,Fema
le_R1, Female_R2,Female_R3
>
>
> How can I do?
>
>
>
>
>
>
>
>
>
>
>
> Raw data:----------------------------------------
> CheckID GradeID Gender ResultID
> 101 1 Male 1
> 102 2 Male 1
> 103 4 Male 3
> 104 3 Female 2
> 105 1 Female 2
> 106 3 Female 3
> 107 5 Female 3
> 108 6 Male 1
> 109 1 Male 1
> 110 2 Male 1
> 111 4 Male 3
> 112 3 Female 2
>
>

Tom Moreau

2006-02-28, 8:23 pm

From AdventureWorks:

WITH CTE
AS
(
SELECT
t.TerritoryID
, t.Name
, Year (h.OrderDate) [Year]
, datepart (qq, h.OrderDate) Quarter
, sum (d.LineTotal) LineTotal
, count (distinct h.SalesOrderID) Orders
FROM
Sales.SalesTerritory t
join Sales.Customer c on c.TerritoryID = t.TerritoryID
join Sales.SalesOrderHeader h on h.CustomerID = c.CustomerID
join Sales.SalesOrderDetail d on d.SalesOrderID = h.SalesOrderID
group by
t.TerritoryID
, t.Name
, Year (h.OrderDate)
, datepart (qq, h.OrderDate)
)
, Sales
AS
(
SELECT
TerritoryID
, Name
, [Year]
, [1] as Q1Sales
, [2] as Q2Sales
, [3] as Q3Sales
, [4] as Q4Sales
FROM
(
SELECT
TerritoryID
, Name
, [Year]
, Quarter
, LineTotal
FROM
CTE
) x
PIVOT
(
MIN (LineTotal)
FOR Quarter IN ([1], [2], [3], [4])
) pvt
)
, Orders
AS
(
SELECT
TerritoryID
, [Year]
, [1] as Q1Orders
, [2] as Q2Orders
, [3] as Q3Orders
, [4] as Q4Orders
FROM
(
SELECT
TerritoryID
, [Year]
, Quarter
, Orders
FROM
CTE
) x
PIVOT
(
MIN (Orders)
FOR Quarter IN ([1], [2], [3], [4])
) pvt
)
SELECT
s.TerritoryID
, s.Name
, s.[Year]
, s.Q1Sales
, o.Q1Orders
, s.Q2Sales
, o.Q2Orders
, s.Q3Sales
, o.Q3Orders
, s.Q4Sales
, o.Q4Orders
FROM
Sales s
JOIN Orders o on o.TerritoryID = s.TerritoryID
and o.[Year] = s.[Year]
order by
TerritoryID
, [Year]
go

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"ad" <flying@wfes.tcc.edu.tw> wrote in message
news:%23TwmvuGPGHA.2236@TK2MSFTNGP15.phx.gbl...
Thanks,
Could you give me an example?

"Tom Moreau" <tom@dont.spam.me.cips.ca>
???????:%23pzKKXyOGHA.2604@TK2MSFTNGP09.phx.gbl...
> Use two CTE's - one for the Male pivot and the other for the Female pivot.
> Then do a join on GradeID between the two.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "ad" <flying@wfes.tcc.edu.tw> wrote in message
> news:evBN5wxOGHA.3144@TK2MSFTNGP11.phx.gbl...
> I have a health check result table for students in a primary school.
> The table is like below.
> I use pivot to count the students count for every resultID:
>
>
> Select GradeID, Gender ,[1] AS r1,[2] as r2 ,[3] as r3
> FROM
> (SELECT CheckID, GradeID, Gender, ResultID
> FROM [Result]) p
> PIVOT
> (
> COUNT (CheckID)
> FOR ResultID IN
> ( [1], [2], [3])
> ) AS pvt
> ORDER BY GradeID;
>
> and get the pivot table:
> ----------------------Pivot on ResultID-----------------------
> GradeID, Gender, R1, R2,R3
> 1 Female 1 3 0
> 1 Male 4 0 0
> 2 Male 3 1 0
> 3 Female 0 4 4
> 4 Male 0 0 4
> 5 Female 0 0 4
> 6 Male 3 1 0
>
> It can only pivot on ResultID, but if I want to Pivot on Gender and
> ResultID, and get the pivot table like:
>
> GradeID, Male_R1, Male_R2,Male_R3,Fema
le_R1, Female_R2,Female_R3
>
>
> How can I do?
>
>
>
>
>
>
>
>
>
>
>
> Raw data:----------------------------------------
> CheckID GradeID Gender ResultID
> 101 1 Male 1
> 102 2 Male 1
> 103 4 Male 3
> 104 3 Female 2
> 105 1 Female 2
> 106 3 Female 3
> 107 5 Female 3
> 108 6 Male 1
> 109 1 Male 1
> 110 2 Male 1
> 111 4 Male 3
> 112 3 Female 2
>
>

Sponsored Links