Translate select columns in a row to multiple rows

Question

i've spent a few hours tinkering with this problem to no avail. I have a dataset with rows that i'd like to subselect specific columns of into a different dataset.

The issue i'm having is translating the AT,BB,BO,ES,LK into a single partner column while grouped by FID and time.

I have been able to reproduce most of this dataset on my own. The AT... columns are provided initially as a 0, 1, 5 or 10. The only data I am curious about is 1s and 5s, and cast as the column name. The inital dataset is produced by this query:

select FID, `time`, 
if((`AT` != 0 and `AT` < 10), "AT", "") as `AT`,
if((`BB` != 0 and `BB` < 10), "BB", "") as `BB`,
if((`BO` != 0 and `BO` < 10), "BO", "") as `BO`,
if((`BT` != 0 and `BT` < 10), "BT", "") as `BT`,
if((`ES` != 0 and `ES` < 10), "ES", "") as `ES`
from f_data group by FID, `time`;

Example Input

`FID`  `time` `AT``BB``BO``ES``LK`
BT201   7:00   AT  BB      ES  LK  
BT201   7:15   AT  BB  BO      LK  
BT201   7:30           BO  ES  LK  
BT201   7:45   AT  BB  BO  ES  LK  
BT201   8:00   AT      BO  ES  LK

Desired Output

`FID`  `time` `partner`
BT201   7:00   AT
BT201   7:00   BB
BT201   7:00   ES
BT201   7:00   LK
BT201   7:15   AT
BT201   7:15   BB
BT201   7:15   BO
BT201   7:15   LK

I've been able to produce this output via R using plyr but haven't been able to produce this output in pure MySQL. If required the mysql --version output is as follows:

Ver 14.14 Distrib 5.5.58, for debian-linux-gnu (x86_64) using readline 6.2

Hi @Ibu, unfortunately this data is provided in a view that I cannot view or modify the structure of. — cmh88, Jan 26 '18 at 01:15
In order to use mysql, you need to be able to write a query. — Ibu, Jan 26 '18 at 01:16
@Ibu I'll format the query i've reproduced the dataset in the initial question. — cmh88, Jan 26 '18 at 01:19
Possible duplicate of [Transposing Dynamic Columns to Rows](https://stackoverflow.com/questions/16359345/transposing-dynamic-columns-to-rows) — P.Salmon, Jan 26 '18 at 02:07
I was able to complete this using the information found in https://stackoverflow.com/questions/15184381/mysql-turn-table-into-different-table This can likely be marked as duplicate due to that answer. Thank you for assistance. — cmh88, Jan 26 '18 at 02:43

score 0 · Answer 1 · answered Jan 26 '18 at 05:59

select FID, `time`, `AT` as `partner`
from input_table
where `AT` = "AT"

union all

select FID, `time`, `BB` as `partner`
from input_table
where `BB` = "BB"

union all

select FID, `time`, `BO` as `partner`
from input_table
where `BO` = "BO"

union all

select FID, `time`, `ES` as `partner`
from input_table
where `ES` = "ES"

union all

select FID, `time`, `LK` as `partner`
from input_table
where `LK` = "LK"

order by FID, `time`, `partner`

Translate select columns in a row to multiple rows

Example Input

Desired Output

1 Answers1