SQL:pivot: convert each row into many rows depends on a condition

Question

I have been trying to solve this but could not figure it out.

so this is the table and more columns could be added:

+------------+-----------+------------+-----------+------------+------------+
| patient_ID | code_ID   | happy?     | mad?      |  smiling?  |    scared? |
+------------+-----------+------------+-----------+------------+------------+
|     kkk    |  kgg      | 1          |    0      |    1       |      1     |
+------------+-----------+------------+-----------+------------+------------+
|     2k2    |  2g2      | 0          |    1      |    0       |      1     |
+------------+-----------+------------+-----------+------------+------------+

So if the value is one then I will display it in a different row but with the value being the column header itself My boss suggested using pivot to solve this but I am open to any way.

output should be like this:

+------------+-----------+------------+
| patient_ID | code_ID   | segment    | 
+------------+-----------+------------+
|     kkk    |  kgg      | happy?     |
+------------+-----------+------------+
|     kkk    |  kgg      | smiling?   | 
+------------+-----------+------------+
|     kkk    |  kgg      | scared?    |
+------------+-----------+------------+
|     2k2    |  2g2      | mad?       | 
+------------+-----------+------------+
|     2k2    |  2g2      | scared?    |
+------------+-----------+------------+

StackOverflow is not a free coding service. You're expected to [try to solve the problem first](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users). Please update your question to show what you have already tried in a [mcve]. For further information, please see [ask], and take the [tour] :) — Barmar, Dec 24 '19 at 23:48
https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns should be a helpful starting place. — Barmar, Dec 24 '19 at 23:49

score 2 · Accepted Answer · answered Dec 25 '19 at 00:05

One method is union all:

select patient_id, code_id, 'happy?' as segment
from t
where `happy?` = 1
union all
select patient_id, code_id, 'mad?'
from t
where `mad?` = 1
union all
select patient_id, code_id, 'smiling?'
from t
where `smiling?` = 1
union all
select patient_id, code_id, 'scared?'
from t
where `scared?` = 1;

SQL:pivot: convert each row into many rows depends on a condition

1 Answers1