convert mysql row to column

Question

I need to convert row values as columns with group by Date

select DATE(TimeStamp) as date ,Status ,count(Status) as value from  abc where (TIMESTAMP BETWEEN '2017-01-00 00:00:00' and '2018-10-09 23:59:59') AND  Name='xyz' GROUP BY DATE(TimeStamp),Status limit 15;

output

+------------+--------------+-------+
| date       | Status       | value |
+------------+--------------+-------+
| 2017-08-09 | running      |   193 |
| 2017-08-09 | stop         |   726 |
| 2017-08-09 | UnknownError |   814 |
| 2017-08-10 | running      |   417 |
| 2017-08-10 | stop         |  1584 |
| 2017-08-10 | UnknownError |  1776 |
| 2017-08-11 | running      |   419 |
| 2017-08-11 | stop         |  1584 |
| 2017-08-11 | UnknownError |  1776 |
| 2017-08-12 | running      |   418 |
| 2017-08-12 | stop         |  1584 |
| 2017-08-12 | UnknownError |  1776 |
| 2017-08-13 | running      |   420 |
| 2017-08-13 | stop         |  1584 |
| 2017-08-13 | UnknownError |  1776 |
+------------+--------------+-------+

Expected Output:

+------------+--------------+---------+--------------+
| date       | stop         | running |UnknownError  |
+------------+--------------+---------+--------------+
| 2017-08-09 |  726         |   193   |814           |
+------------+--------------+---------+--------------+

Try this select DATE(TimeStamp) as date ,Status ,count(Status) as value from abc where (TIMESTAMP BETWEEN '2017-01-00 00:00:00' and '2018-10-09 23:59:59') AND Name='xyz' GROUP BY date,Status limit 15; — MD. Jubair Mizan, Dec 10 '18 at 11:11

score 0 · Answer 1 · answered Dec 10 '18 at 11:33

0

Look at turning your query into a PIVOT.

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

And dupe of Convert Rows to columns using 'Pivot' in SQL Server

answered Dec 10 '18 at 11:33

SamuelB

157
9

The OP uses MySQL, not SQL Server: are you sure that syntax is exactly the same? – Cœur Dec 20 '18 at 05:52
http://webdevzoom.com/pivot-table-using-mysql/ should offer guidance in that respect. – SamuelB Dec 20 '18 at 14:35

score 0 · Accepted Answer · answered Dec 20 '18 at 05:21

select Date(Timestamp),round(AVG(case when Status='stop' then stop else 0 end),2) 'stop',round(AVG(case when NodeName='running' then running else 0 end),2) 'running' from abc  where (Timestamp >= '2018-08-09' AND Timestamp<='2018-08-13') group by Date(Timestamp);

convert mysql row to column

2 Answers2