Click here to Skip to main content
15,860,844 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi guys,

I have a query output like this,
-------------------------
HTML
Status	   PP	AR	BL	EN
Active	   3	2	2	1
In-Active	1	2	2	3

------------------
But i want the output like this,
HTML
Status    Total PP AR BL EN
Active    8     3   2  2  1
In-Active 8     1   2  2  3


Any help is appreciated.

NOTE: Here the "Total" column is the sum of the all teams count.

What I have tried:

SQL
SELECT status, [PP],[AR],[BL], [EN] FROM
(SELECT status, team FROM tablename) as src
PIVOT
(
   COUNT(team) FOR team IN ([PP],[AR],[BL], [EN])
) AS output
Posted
Updated 22-Feb-17 9:24am
v2
Comments
Maciej Los 22-Feb-17 15:24pm    
You're pretty close. Please, see my answer.

Use a CTE table to get the count of each status, then join it with the original table for pivot, i.e.:
WITH CTETABLE (status, total) as
(
    SELECT status, count(status) from tablename group by status
    
) SELECT status, total, [PP],[AR],[BL], [EN] FROM
(SELECT c.status, c.total, team FROM tablename t join CTETABLE c on t.status=c.status) as src
PIVOT
(
   COUNT(team) FOR team IN ([PP],[AR],[BL], [EN])
) AS output
 
Share this answer
 
v3
Comments
Bryian Tan 22-Feb-17 10:54am    
nice!!!
Maciej Los 22-Feb-17 15:24pm    
5ed!
Raj 0203 23-Feb-17 2:04am    
Noop..! This is not working for me, as it is giving an output like,

status total PP AR BL EN
Active 10 2 0 0 0
In-Active 7 2 0 0 0
--------
Pls Help..
Peter Leow 23-Feb-17 3:27am    
Yes, it works. Help yourself by checking your table data.
Raj 0203 23-Feb-17 6:14am    
Thank you, its perfect.
Another way (alternative to Peter Leow[^]) is:
SQL
SELECT status, [PP]+[AR]+[BL]+[EN] AS Total, [PP], [AR], [BL], [EN]
FROM (
    SELECT status, [PP],[AR],[BL], [EN]
    FROM (
        SELECT status, team FROM tablename
        ) as src
    PIVOT (COUNT(team) FOR team IN ([PP],[AR],[BL], [EN])) AS output
) AS Final
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900