MySQL Select multiple column depending on column value

Question

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".

You can't return a different number of columns for different rows in the result. — Barmar, Jun 03 '16 at 15:41
@Barmar Well you can unpivot and dinamic pivot again. but is messy. — Juan Carlos Oropeza, Jun 03 '16 at 15:43
Check this one, to see how unpivot your table http://stackoverflow.com/questions/15184381/mysql-turn-table-into-different-table — Juan Carlos Oropeza, Jun 03 '16 at 16:07

Barmar · Accepted Answer · 2016-06-03T15:49:08.207

1

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.

edited Jun 03 '16 at 15:49

answered Jun 03 '16 at 15:43

Barmar

741,623
53
500
612

True. If you want to put something in its place, use `IFNULL(e, 'something')` – Barmar Jun 03 '16 at 15:45
I think this solution isn't my favorite. I need to work with those column in php, so exploding and renaming them isn't beautiful :( Hopefully someone got another idea... – chack Jun 03 '16 at 15:48
I've added another solution using `UNION`. – Barmar Jun 03 '16 at 15:49
2

Why don't you just return all the columns, and then distinguish between the cases in PHP? – Barmar Jun 03 '16 at 15:50
I'm thinking of performance... Is there a noticeable difference in selecting 50 columns (20 of them null) and working with them in php or selecting 20 with mysql doing the work? – chack Jun 03 '16 at 15:55
It depends on how big the data in those columns is, how many rows you're selecting, and the speed of the connection between the application and database (are they on the same machine or connected via a network?). – Barmar Jun 03 '16 at 15:56
General recommendation: first implement the simple design. If performance is a problem, then worry about optimizing it. – Barmar Jun 03 '16 at 15:57
approx max 30 rows, mainly numerals with max 10 length and some varchar(100). I would do the select and sorting in php on the same machine. – chack Jun 03 '16 at 15:59
A silk purse from a pig's ear – Strawberry Jun 03 '16 at 16:00
Or, return all of the columns, but just replace the value with NULL when it's not needed... `SELECT a, IF(a IN (1,2,5,7),b,NULL) AS b, IF(a IN (2,3,6,8),c,NULL) AS c, ` – spencer7593 Jun 03 '16 at 16:02
@chack For such a small query, performance differences should be negligible. Don't even start to worry about performance until you get into the thousands. – Barmar Jun 03 '16 at 16:03
alright, thanks everyone! Will doing the work with php :) – chack Jun 03 '16 at 16:06

MySQL Select multiple column depending on column value

1 Answers1