How to combine multiple columns into one column in SQL?

Question

Based on the Google Form input I have the following data collected from the user

+-------+--------+--------+--------+ | Name | Col1 | Col2 | Col3 | +-------+--------+--------+--------+ | name1 | | | 1 | | name2 | 3 | | | | name3 | | 2 | | +-------+--------+--------+--------+

which only one of the Col1, Col2 or Col3 will contain value

What I want is to created a view like this

+-------+--------+ | Name | Col | +-------+--------+ | name1 | 1 | | name3 | 2 | | name2 | 3 | +-------+--------+

The SQL command should not only merge Col1, Col2 and Col3 but also sort the new Col based on it value.

Thanks in advance

Gordon Linoff · Accepted Answer · 2018-03-19 02:07:39Z

2

You can use coalesce():

select name, coalesce(col1, col2, col3) as col from t order by col;

answered Mar 19, 2018 at 2:07

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ttarchala Over a year ago

How come this works? Google Sheet's SQL does not support coalesce().

ttarchala · Accepted Answer · 2018-03-19 09:37:09Z

The simplest method below (I'-'I's answer is good but a little overcomplicated) given the particularities of your data

=sort(arrayformula({H1:H6, I1:I6 + J1:J6 + K1:K6}), 2, true)

TheMaster · Accepted Answer · 2018-03-19 09:11:49Z

Sheets doesn't support full sql.

=ARRAYFORMULA(SORT({A2:A4,MMULT(--B2:D4,TRANSPOSE(B2:D2^0))},2,1))

Collectives™ on Stack Overflow

How to combine multiple columns into one column in SQL?

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related