chack chack - 6 months ago 6
SQL Question

MySQL Select multiple column depending on column value

I searched the Internet and whole Stackoverlow twice, but didn't find a working solution.

Say I have a Table with following Columns:
a,b,c,d,e,f,g,h,i

a is an integer which goes from 1-16
Depending on this value i have to choose if i select b-i.

Example:
if a = 1 I need b,c,d,e,i. if a = 2 I need e,g,h,i

So I could use:

SELECT
CASE a WHEN 1 THEN b WHEN 2 THEN NULL ELSE NULL END as a
....
CASE a WHEN 1 THEN NULL WHEN 2 THEN g ELSE NULL END as g


Isn't there an easier way like:

CASE a When 1 (select b,c,d,e,i)?


Bonus wish: Can I select as depending on value of a? Example

if a = 1 column b should be named "lol", if a = 2 b should be called "rofl".

Answer

The closest I can think of to what you want would be to concatenate the columns you want:

SELECT CASE a
           WHEN 1 THEN CONCAT_WS(', ', b, c, d, e, i)
           WHEN 2 THEN CONCAT_WS(', ', e, g, h, i)
       END AS result

Another solution is to use UNION

SELECT a, b, c, d, e, i
FROM yourTable
WHERE a = 1
UNION
SELECT a, e, g, h, i, null
FROM yourTable
WHERE a = 2

Note that you can't have different column aliases depending on the value of a. There's just one set of column names for the entire query. In a UNION, they come from the column names or aliases in the first subquery.

Comments