CODEWITHSUNDEEP
mysqlpivot-table
egidiocs
egidiocs

Reputation: 2847

Joined results as column names

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!

Upvotes: 1

Views: 116

Answers (3)

newtover
newtover

Reputation: 32094

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;

Upvotes: 1

Michael
Michael

Reputation: 1231

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.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562701

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.

Upvotes: 3

Related Questions