MySQL Column headers into a single column, second column for the values

Question

I currently have a table that shows the data I need in the format of

SELECT * FROM XYZ;

Date | Brand | Game1| Game2 | Game3  |  
2017 | xxxxx  | xxxxxx  | ---------- | -----------|

2017 | xxxxx  | ----------| yyyyyyy | -----------|

2017 | xxxxx  | ----------| -----------| yyyyyyy |

The way I would like the data presented is with 4 columns in total, like this:

Date | Brand | GameName | Value

2017 | xxxxx | Game1 | 1234567.89

2017 | xxxxx | Game2 | 98.7654321

2017 | xxxxx | Game3 | 12345987.65

The reason for doing this is so that the data becomes easier to filter through to find specific values.

I've tried most other suggestions on SO but haven't had much success yet

(Such as group_concat).

One suggestion that did work was doing Union all, however I need a dynamic query as the table will continue to be added to in the future.

Currently I'm trying to set up a cursor to do it.

What are all those dashes? Are they really dashes or is something else there, e.g. empty string or null? — Tim Biegeleisen, Sep 25 '17 at 08:16
In this question someone asked a similar question. The keyword here is `transponding` or `unpivot`. https://stackoverflow.com/questions/31238717/unpivot-dynamic-columns-to-rows-missing-data-from-last-columns — Jan Zeiseweis, Sep 25 '17 at 08:18

score 2 · Answer 1 · answered Sep 25 '17 at 08:20

2

If your table will keep having new columns then whichever query you use, you will have to first get all the columns from INFORMATION_SCHEMA.TABLE, here's an approach:

Get all the column names with %Game% pattern
Use UNION or any other query to get the output

Below is an example:

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='db_name' 
    AND `TABLE_NAME`='table'
    AND COLUMN_NAME LIKE '%Game%';

Now, from the output of this query, construct a dynamic UNION query, e.g.:

SELECT Date, Brand, `<column>`, Value
FROM table

UNION

SELECT Date, Brand, `<column>`, Value
FROM table

answered Sep 25 '17 at 08:20

Darshan Mehta

27,835
7
53
81

How can I get `` into my final table if there is nothing to join it on to? – davo777 Sep 25 '17 at 08:29
You would iterate the result set of the first query and create union queries for the final result. `` is just the value of a row from the first query. – Darshan Mehta Sep 25 '17 at 08:33

Tim Biegeleisen · Answer 2 · 2017-09-25T08:28:44.120

You can do an unpivot combined with an aggregation to generate the output you want. Appreciate that the value you want to compute for each game coincides with the sum of the respective game column. This is so because you said that all other values are zero.

SELECT
    Date,
    MAX(CASE WHEN Game1 <> 0 THEN Brand END) AS Brand,
    'Game1' AS GameName,
    SUM(Game1) AS Value
FROM yourTable
GROUP BY Date
UNION ALL
SELECT
    Date, MAX(CASE WHEN Game2 <> 0 THEN Brand END), 'Game2', SUM(Game2)
FROM yourTable
GROUP BY Date
UNION ALL
SELECT
    Date, MAX(CASE WHEN Game3 <> 0 THEN Brand END), 'Game3', SUM(Game3)
FROM yourTable
GROUP BY Date

Demo here:

Rextester

jidexl21 · Answer 3 · 2017-09-25T08:45:38.547

1

This should work

select Date, Brand, 'Game1', Game1 where Game1 is not null
UNION ALL
select Date, Brand, 'Game2' Game2 where Game2 is not null
UNION ALL
select Date, Brand, 'Game3' Game3 where Game3 is not null

you can save this as a view and query it directly for your results

edited Sep 25 '17 at 08:45

answered Sep 25 '17 at 08:28

jidexl21

589
5
20

MySQL does not support CTE, but in any case your data does not match the data in the question, q.v. the above comments. – Tim Biegeleisen Sep 25 '17 at 08:33
in that case I will remove the cte and restructure the data – jidexl21 Sep 25 '17 at 08:39
That would work but the issue is that I need a dynamic query – davo777 Sep 25 '17 at 08:48

MySQL Column headers into a single column, second column for the values

3 Answers3

Rextester