Joined results as column names

Question

I have this Mysql scenario

table1
--------------
id   `7`   `8`
--------------
1    10    20
2    20    30

table 2
---------------
id  code   rel
---------------
3     7     1
4     8     2

I am using following statement to get values

SELECT t2.id, t2.code, 
CASE t2.code WHEN 7 THEN (SELECT `7` FROM table1 t1 where t1.id = t2.rel)
CASE t2.code WHEN 8 THEN (SELECT `8` FROM table1 t1 where t1.id = t2.rel) 
END as val
FROM table2 t2

but it's neither pretty or functional, because I need these values summed, multiplied, etc, and there are a lot of columns.

Is there a way to join these tables and get table1.7 value for t2.rel,t2.code values? Something similar to

SELECT t2.id, t2.code, eval(t1.(t2.code))  as val
FROM table2 t2 
JOIN table1 t1 on t2.rel = t1.id

Thank you very much!

Bill Karwin · Answer 1 · 2011-12-07T22:12:17.760

3

Every column referenced in an SQL query must be fixed at the time you prepare the query. There's no such thing as eval.

However you can use a data value to drive a CASE statement:

SELECT t2.id, t2.code,
  CASE t2.code WHEN '7' THEN t1.`7` WHEN '8' THEN t1.`8` END AS `val`
FROM table2 t2
JOIN table1 t1 ON t2.rel = t1.id;

But you'd have to hard-code all the cases before you prepare the query. There's no way for SQL to generate the expressions during execution time as it finds new code values on successive rows of data.

edited Dec 07 '11 at 22:12

answered Dec 07 '11 at 22:06

Bill Karwin

538,548
86
673
828

I misunderstood the original question, so I rewrote my answer. – Bill Karwin Dec 07 '11 at 22:12
yes, it's better to join and case, rather than case then select. – egidiocs Dec 07 '11 at 22:43

Michael · Answer 2 · 2011-12-17T17:55:14.700

1

Ok I'm curious if it is possible what your trying to do but this might be a solution for now. Do a select like this:

SELECT t2.id, t2.code, t1.*
FROM table2 t2 
JOIN table1 t1 on t2.rel = t1.id

Resulting in

  || *id* || *code* || *id* || *7* || *8* ||
  ------------------------------------------
  || 3    || 7      || 1    || 10  || 20  ||
  || 4    || 8      || 2    || 20  || 30  ||

Then in your code concatenate in your result. If your using php and your result is associative:

echo $result[$result['code']];

I don't think your going to make this happen in a query. You might want to rethink your database design.

edited Dec 17 '11 at 17:55

answered Dec 07 '11 at 22:06

Michael

1,201
1
8
15

yes, the best approach is to redesign database table1 table. bbut it's not a option at this time.... anyway, thank you! – egidiocs Dec 07 '11 at 22:42

score 1 · Accepted Answer · answered Dec 17 '11 at 19:14

I don't know if it is still relevant.

As you alredy know, there is no such thing as pivot in MySql, but aggregations might look rather easy if the number of columns is defined:

SELECT
  SUM(IF(t2.code = 7, `7`, NULL)) as sum7,
  AVG(IF(t2.code=8, `8`, NULL)) as avg8
FROM t1 JOIN t2
  ON t1.id = t2.rel;

Joined results as column names

3 Answers3