Merge multiple columns into one column with multiple rows

Question

In PostgreSQL, how can I merge multiple columns into one column with multiple rows?

The columns are all boolean, so I want to:

Filter for true values only
Replace the true value (1) with the name of the column (A, B or C)

I have this table:

ID   | A   | B   | C
1      0     1     0
2      1     1     0
3      0     0     1
4      1     0     1
5      1     0     0 
6      0     1     1

I want to get this table:

ID   | Letter
1      B
2      A
2      B
3      C
4      A
4      C
5      A
6      B
6      C

Emerson Dallagnol · Accepted Answer · 2017-01-29T01:48:26.367

3

I think you need something like this:

SELECT ID, 'A' as Letter FROM table WHERE A=1
UNION ALL
SELECT ID, 'B' as Letter FROM table WHERE B=1
UNION ALL
SELECT ID, 'C'as Letter FROM table WHERE C=1
ORDER BY ID, Letter

edited Jan 29 '17 at 01:48

answered Jan 29 '17 at 01:32

Emerson Dallagnol

1,269
1
12
21

That's not sorted. – Grzegorz Górkiewicz Jan 29 '17 at 01:33
Now only the resultset of the 3rd query is ordered. – Grzegorz Górkiewicz Jan 29 '17 at 01:37
And `'A'`, `'B'`, `'C'` are not under `Letter`. – Grzegorz Górkiewicz Jan 29 '17 at 01:39
1

The "ORDER BY" clause applies to the entire UNION: http://stackoverflow.com/questions/4715820/how-to-order-by-with-union – Emerson Dallagnol Jan 29 '17 at 01:49
1

very nice solution. – Shiping Jan 29 '17 at 02:01

score 1 · Answer 2 · answered Jan 29 '17 at 01:44

1

SELECT ID,
(CASE 
    WHEN TABLE.A = 1 then 'A' 
    WHEN TABLE.B = 1 then 'B'
    WHEN TABLE.C = 1 then 'C' 
    ELSE NULL END) AS LETTER
from TABLE

answered Jan 29 '17 at 01:44

ollaw

2,086
1
20
33

If A=1 and B=1 in the same row, your query will return only 1 row with 'A'. – Emerson Dallagnol Jan 29 '17 at 01:50
1

You're right, i thought there was only one '1' for time – ollaw Jan 29 '17 at 01:52
looks good. but it only gets one from each row even if there are more than one column with 1. – Shiping Jan 29 '17 at 02:03
Welcome to Stack Overflow! While you may have solved this user's problem, code-only answers are not very helpful to users who come to this question in the future. Please edit your answer to explain why your code solves the original problem. – Joe C Jan 29 '17 at 08:19

score 1 · Answer 3 · answered Jan 29 '17 at 01:46

You may try this.

 insert into t2 select id, 'A' from t1 where A=1;
 insert into t2 select id, 'B' from t2 where B=1;
 insert into t2 select id, 'C' from t3 where C=1;

If you care about the order, then you can do this.

insert into t3 select id, letter from t2 order by id, letter;

Vivek S. · Answer 4 · 2017-01-29T05:47:06.277

W/o UNION

You can use a single query to get the desired output.Real time example

select id
    ,regexp_split_to_table((
            concat_ws(',', case 
                    when a = 0
                        then null
                    else 'a'
                    end, case 
                    when b = 0
                        then null
                    else 'b'
                    end, case 
                    when c = 0
                        then null
                    else 'c'
                    end)
            ), ',') l
from c1;

regexp_split_to_table() & concat_ws()

Merge multiple columns into one column with multiple rows

4 Answers4