13,504,308 members
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
Maciej Los 22-Feb-17 15:24pm

## 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```
v3
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

Raj 0203 23-Feb-17 6:14am

Thank you, its perfect.
Karthik Bangalore 23-Feb-17 12:24pm

5

## 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```

Top Experts
Last 24hrsThis month
 OriginalGriff 138 Wendelius 88 John Simmons / outlaw programmer 75 Richard MacCutchan 70 Richard Deeming 55
 OriginalGriff 3,963 Jochen Arndt 2,328 ppolymorphe 2,013 Maciej Los 1,654 Wendelius 1,558