Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databases
How to pivot on two fieldI 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
Post Follow-up to this message
Re: How to pivot on two fieldUse 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
Post Follow-up to this message Re: How to pivot on two fieldThanks, 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 > >
Post Follow-up to this message Re: How to pivot on two fieldFrom 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 > >
Post Follow-up to this message
Sponsored Links
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread
