MySQL Format as rows as columns

Question

I am working on an updated internal website that will pull data from a sql server. The data is automatically inserted into the database from a VAX system. The raw database looks like this:

mysql> SELECT * FROM userdb.clientdata WHERE datadate = '2016-09-23' AND header IN (1,3,6,9,212,2048);
+--------+--------------+-------------+----------+--------+-----------+-------------------------+---------------+---------------+
| id     | clientnumber | plantnumber | datahour | header | datavalue | datadate                | projectnumber | dec_precision |
+--------+--------------+-------------+----------+--------+-----------+-------------------------+---------------+---------------+
|  28673 |         NULL |           2 |        1 |      1 | -5865.97  | 2016-09-23 00:00:00.000 |          NULL |             1 |
|  28675 |         NULL |           2 |        1 |      3 | 78.368    | 2016-09-23 00:00:00.000 |          NULL |             1 |
|  28678 |         NULL |           2 |        1 |      6 | -5865.97  | 2016-09-23 00:00:00.000 |          NULL |             1 |
|  28681 |         NULL |           2 |        1 |      9 | -5865.97  | 2016-09-23 00:00:00.000 |          NULL |             1 |
|  28884 |         NULL |           2 |        1 |    212 | 0         | 2016-09-23 00:00:00.000 |          NULL |             0 |
|  30720 |         NULL |           2 |        1 |   2048 | 0         | 2016-09-23 00:00:00.000 |          NULL |             0 |
|  30721 |         NULL |           2 |        2 |      1 | -5865.97  | 2016-09-23 00:00:00.000 |          NULL |             1 |
|  30723 |         NULL |           2 |        2 |      3 | 77.342    | 2016-09-23 00:00:00.000 |          NULL |             1 |
|  30726 |         NULL |           2 |        2 |      6 | -5865.97  | 2016-09-23 00:00:00.000 |          NULL |             1 |
|  30729 |         NULL |           2 |        2 |      9 | -5865.97  | 2016-09-23 00:00:00.000 |          NULL |             1 |
|  30932 |         NULL |           2 |        2 |    212 | 0         | 2016-09-23 00:00:00.000 |          NULL |             0 |
|  32768 |         NULL |           2 |        2 |   2048 | 0         | 2016-09-23 00:00:00.000 |          NULL |             0 |

I need to be able to format it so the result looks like this:

+-------------------------+----------+---------+---------+---------+---------+-----------+------------+
| datadate                | datahour | header1 | header3 | header6 | header9 | header212 | header2048 |
+-------------------------+----------+---------+---------+---------+---------+-----------+------------+
| 2016-09-23 00:00:00.000 |        1 | -5865.97|  78.368 | -5865.97| -5865.97|         0 |          0 |
| 2016-09-23 00:00:00.000 |        2 | -5865.97|  77.342 | -5865.97| -5865.97|         0 |          0 |

Any assistance would be appreciated.

MySQL 5.7.21 for Linux

Edit: The website will be using php, but I am looking for the actual MySQL Query to provide the result listed so I can store it in another table.

WHat you are asking for is known as 'pivot', likely the most frequent SQL question on Stack. https://stackoverflow.com/questions/7674786/mysql-pivot-table or google 'MYSQL Pivot StackOverflow'. — Twelfth, Mar 13 '18 at 20:16

jjabba · Answer 1 · 2018-03-13T20:58:31.267

1

You should be able to group by datahour and customize rows for headers like this:

SELECT 
    datadate, 
    datahour, 
    SUM(IF(header = '1', datavalue, 0)) as header1,
    SUM(IF(header = '3', datavalue, 0)) as header3,
    SUM(IF(header = '6', datavalue, 0)) as header6,
    SUM(IF(header = '9', datavalue, 0)) as header9,
    SUM(IF(header = '212', datavalue, 0)) as header212,
    SUM(IF(header = '2048', datavalue, 0)) as header2048
FROM userdb.clientdata
WHERE datadate = '2016-09-23' AND header IN (1,3,6,9,212,2048)
GROUP BY datahour;

This solution make an important assumptions about your data: no duplicate entries for a specific header in one "datahour"

edited Mar 13 '18 at 20:58

answered Mar 13 '18 at 20:11

jjabba

494
3
16

The result layout looks correct, but the data does not appear to be correct. Only header1 has data all other headers have 0 [Example](https://pastebin.com/P2DT97rZ) – Michael Mar 13 '18 at 20:25
selecting datadate, datahour, but only grouping by datahour isn't the best practice...only really works in MySQL, other RDBMS's correctly return an error here. – Twelfth Mar 13 '18 at 20:29
@Michael Fixed the issue with missing values by adding SUM aggregation. It's not very pretty, but assuming no one header has more then one entry per datahour it will do the job. – jjabba Mar 13 '18 at 21:05

score 1 · Accepted Answer · answered Mar 13 '18 at 20:29

1

You can pivot in MySQL using conditional aggregation:

SELECT datadate, datahour, 
       MAX(CASE WHEN header = 1 THEN datavalue END) as header1,
       MAX(CASE WHEN header = 3 THEN datavalue END) as header3,
       MAX(CASE WHEN header = 6 THEN datavalue END) as header6,
       MAX(CASE WHEN header = 9 THEN datavalue END) as header9,
       MAX(CASE WHEN header = 212 THEN datavalue END) as header212,
       MAX(CASE WHEN header = 2048 THEN datavalue END) as header2048
FROM userdb.clientdata
WHERE datadate = '2016-09-23' AND header IN (1, 3, 6, 9, 212, 2048)
GROUP BY datadate, datahour
ORDER BY datadate, datahour;

answered Mar 13 '18 at 20:29

Gordon Linoff

1,242,037
58
646
786

The result layout looks correct, however the data is incorrect. The all header points have either "0" or an incorrect value. See Output: [Output](https://pastebin.com/Aei7E8qY) – Michael Mar 13 '18 at 20:33
EDIT: This worked with minor adjustment to MAX(Case) - Marking as correct answer - Thank you very much – Michael Mar 13 '18 at 20:41

score 0 · Answer 3 · answered Mar 13 '18 at 22:05

Here is one approach. I use ucd to represent userdb.clientdata in your question.

SELECT DATE_FORMAT(a.datadate, '%Y-%m-%d %T.%s') datadate, a.datahour,
(SELECT datavalue FROM ucd WHERE a.datahour = datahour and header = 1)  header1,
(SELECT datavalue FROM ucd WHERE a.datahour = datahour and header = 3)  header3,
(SELECT datavalue FROM ucd WHERE a.datahour = datahour and header = 6)  header6,
(SELECT datavalue FROM ucd WHERE a.datahour = datahour and header = 9)  header9,
(SELECT datavalue FROM ucd WHERE a.datahour = datahour and header = 212)  header212,
(SELECT datavalue FROM ucd WHERE a.datahour = datahour and header = 2048)  header2048
FROM (SELECT DISTINCT datadate, datahour FROM ucd) a
WHERE a.datadate = '2016-09-23 00:00:00.000'

I will try to explain step by step and please correct me if there is error.

First select distinct datahour in your table.
Then find the datavalue when header equals to the certain value (1,3,6,etc.) and fill in corresponding column.

RESULT

datadate                datahour    header1   header3   header6    header9  header212   header2048
2016-09-23 00:00:00.00     1       -5865.97   78.368    -5865.97   -5865.97 0           0
2016-09-23 00:00:00.00     2       -5865.97   77.342    -5865.97   -5865.97 0           0

Please find the code in SQL Fiddle

MySQL Format as rows as columns

3 Answers3