Click here to Skip to main content
13,146,739 members (81,640 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi guys,

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

------------------
But i want the output like this,
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:

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 22-Feb-17 4:22am
Updated 22-Feb-17 9:24am
v2
Comments
Maciej Los 22-Feb-17 15:24pm
   
You're pretty close. Please, see my answer.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
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.
Karthik Bangalore 23-Feb-17 12:24pm
   
5
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Another way (alternative to Peter Leow[^]) is:
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
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web01 | 2.8.170915.1 | Last Updated 22 Feb 2017
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100