Complicated SELECT

Question

Maybe I'm going in the wrong direction with this, so I'd love some pointers on how to approach this differently.

I have multiple MySql database tables with 50+ TEXT fields (categories) each (reason, problem, cause, disposition ... etc.), some (most) of these are null, some aren't, depending on the item they are describing.

I want to convert these to a 'topic', 'text' table instead. for example

item,  reason  , problem      , cause           , disposition
0001,  null    , broken widget, unknown         , null
0002,  returned, null         , customer unhappy, in transit

to

item, topic      , text
0001, problem    , broken widget
0001, cause      , unknown
0002, reason     , returned
0002, cause      , customer unhappy
0002, disposition, in transit

So, I'm fiddling with a select query, this is what I got so far:

SELECT item, problem AS 'text', 'problem' AS 'topic' FROM my_table
    WHERE problem IS NOT NULL;

how would I go about adding the next category? Like

SELECT item, cause AS 'text', 'cause' AS 'topic' FROM my_table
    WHERE cause IS NOT NULL;

1: how would I add that to the previous select statement so that it would cover both 'cause' and 'problem'?

2: since there are so many categories, is there a way to avoid the manual labor here and iterate through field names somehow?

possible duplicate of [MySQL - turn table into different table](http://stackoverflow.com/questions/15184381/mysql-turn-table-into-different-table) — Allan, Mar 31 '15 at 15:57
I agree that the base question of `how` is answered in the proposed duplicate, but the 2nd question isn't addressed there. — Hart CO, Mar 31 '15 at 16:10
Frankly, if this is a one-time thing. I would write a simple script to do this rather then try to solve with a single SQL statement. That will make it easy to read your current structure into memory and then insert rows into new format based on the various slices of that structure that you desire. — Mike Brant, Mar 31 '15 at 16:12
I actually ended up following Mike Brant's solution. I wrote a PHP script to convert the table. — Piotr, Mar 31 '15 at 17:29

score 1 · Answer 1 · answered Mar 31 '15 at 16:06

You can use UNION ALL:

SELECT item, problem AS text, 'problem' AS topic
FROM my_table
WHERE problem IS NOT NULL
UNION  ALL
SELECT item, cause AS text, 'cause' AS topic 
FROM my_table
WHERE cause IS NOT NULL;

To save some time building the query you could use INFORMATION_SCHEMA to list the columns:

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' 
    AND `TABLE_NAME`='my_table';

Either concatenate the query with the COLUMN_NAME or just paste the list of column names into Excel and build the query there and copy/down.

score 1 · Accepted Answer · answered Mar 31 '15 at 16:10

You need to be very careful about using single quotes in queries. A simple rule: only use single quotes for string and date constants. Otherwise, the escape character is the backtick -- and if you name columns and tables correctly, you don't need that.

So this query:

SELECT item, cause AS 'text', 'cause' AS 'topic'
FROM my_table
WHERE cause IS NOT NULL;

Should be:

SELECT item, cause AS text, 'cause' AS topic
FROM my_table
WHERE cause IS NOT NULL;

Then, you can combine these using union all:

SELECT item, problem AS text, 'problem' AS topic FROM my_table WHERE problem IS NOT NULL UNION ALL
SELECT item, cause AS text, 'cause' AS topic FROM my_table WHERE cause IS NOT NULL

And, you can keep adding subqueries for all the columns you want to include this way.

EDIT:

If you want to do this with information_schema.columns, you can use replace:

select replace('SELECT item, <colname> AS text, ''<colname>'' AS topic FROM my_table WHERE problem IS NOT NULL UNION ALL',
               '<colname>', column_name)
from information_schema.columns c
where table_name = XXX and
      column_name <> 'item';

Thanks for the pointers, I appreciate it. I ended up creating a quick PHP script instead, as it ended up being less complex in the end, but I do appreciate the input. I'll choose this as the answer as it thoroughly explained the information_schema bit. — Piotr, Mar 31 '15 at 17:30

Complicated SELECT

2 Answers2