DB rows need to take in columns

Question

I have a below MySQL table.

id       Creation_Date         Goals  Alternative    Value
-----------------------------------------------------------
1   2014-04-17 10:09:30     G1      A        0.86
2   2014-04-17 10:09:30     G1      B        0.87
3   2014-04-17 10:09:30     G2      A        0.5
4   2014-04-17 10:09:30     G2      B         0

I need a MySQL query for changing rows in columns and also need to their respective values like below.

**Output Require **

Alternative     G1         G2  
------------------------------- 
   A           0.86        0.50
   B           0.87        0

Tried Solution :

I tried below query

select Alternative , max(case when Goals = 'G1' then round(value,2) end) as Goal1, max(case when Goals = 'G2' then round(value,2) end) as Goal2 from sgwebdb.dim_module group by id ;

but this is not giving required output ,this is giving below output

Alternative     G1         G2  
------------------------------- 
   A         0.86         Null
   B         0.87         Null
   A     Null         0.50
   B     Null         0.00

I tried below query select Alternative , max(case when Goals = 'G1' then round(value,2) end) as Goal1, max(case when Goals = 'G2' then round(value,2) end) as Goal2 from sgwebdb.dim_module group by id ; but it is giving me duplicate column — vim, Apr 17 '14 at 04:28

score 1 · Answer 1 · answered Apr 17 '14 at 04:54

try this query:

SELECT Alternative,
SUM(Val*(1-ABS(SIGN(Goals-1)))) AS G1,
SUM(Val*(1-ABS(SIGN(Goals-2)))) AS G2
FROM table1 
GROUP BY Alternative;

the table look like: change G1 to 1 and G2 to 2

id       Creation_Date     Goals  Alternative    Value
-----------------------------------------------------------
1    2014-04-17 10:09:30       1        A             0.86
2    2014-04-17 10:09:30       1        B             0.87
3    2014-04-17 10:09:30       2        A             0.5
4    2014-04-17 10:09:30       2        B             0

the result look like:

Alternative     G1         G2  
------------------------------- 
   A           0.86        0.50
   B           0.87        0

Ravinder ans is working fine and with that ans I don't need to do any changes.your one is also giving desired output but I need to do changes in rows with you ans.thanks — vim, Apr 17 '14 at 06:01

Ravinder Reddy · Accepted Answer · 2014-04-17T05:38:09.273

OP's comment:

I tried below query but it is giving me duplicate column

select Alternative , max(case when Goals = 'G1' then round(value,2) end) as Goal1, max(case when Goals = 'G2' then round(value,2) end) as Goal2 from sgwebdb.dim_module group by id ;

You have to group by alternative for goals being converted to column based on alternative

Query:

select 
 alternative,
 max( case when goals='G1' then round( value, 2 ) end ) as 'G1',
 max( case when goals='G2' then round( value, 2 ) end ) as 'G2'
from sgwebdb.dim_module
group by alternative
;

Result:

+-------------+------+------+
| Alternative | G1   | G2   |
+-------------+------+------+
| A           | 0.86 | 0.50 |
| B           | 0.87 | 0.00 |
+-------------+------+------+

Thanks Ravinder ,this is giving desired output.. – vim Apr 17 '14 at 05:58 — vim, Apr 17 '14 at 05:58

DB rows need to take in columns

2 Answers2