CODEWITHSUNDEEP
mysqlsqlpivoton-the-fly
Sallyerik
Sallyerik

Reputation: 519

Is it possible to pivot the output from a query?

I have this output from this query:

select Date,Status, count(distinct persons)from TableA where Date='2014-11-04' group by Status;

+------------+------------------------+-------------------------------+
| Date       | Status                 | count(distinct persons)       |
+------------+------------------------+-------------------------------+
| 2014-11-04 | 0                      |                            45 |
| 2014-11-04 | 1                      |                            93 |
+------------+------------------------+-------------------------------+

What I wanted to get is that:

+------------+------------------------+-------------------------------+
| Date       | 0                      |     1                         |
+------------+------------------------+-------------------------------+
| 2014-11-04 | 45                     |    93                         |
+------------+------------------------+-------------------------------+

Upvotes: 3

Views: 67

Answers (2)

Neha Patil
Neha Patil

Reputation: 1

You can use following code -

select Date, [0], [1]
from
(select Date,Status,persons
from TableA 
where Date='2014-11-04') AS SourceTable
PIVOT
(
    COUNT(persons)
    FOR Status IN ([0],[1])
) AS PivotTable;

Upvotes: -1

GarethD
GarethD

Reputation: 69789

You can put a condition inside your COUNT function using CASE:

SELECT  Date,
        COUNT(DISTINCT CASE WHEN status = 0 THEN persons END) AS `0`,
        COUNT(DISTINCT CASE WHEN status = 1 THEN persons END) AS `1`
FROM    TableA
WHERE   Date = '2014-11-04'
GROUP BY Date;

Upvotes: 3

Related Questions