Convert rows to column using CASE in mysql

Question

I have a table like this:

ipiresia	program
1	1
1	2
1	3
2	1
2	2
2	3
3	1
3	2
3	3
4	1
4	2

I want to make a query to transform the table like this:

ipiresia	program1	program2	program3
1	x	x	x
2	x	x	x
3	x	x	x
4	x	x

I have used this query but it doesn't work:

SELECT ipiresia, (CASE program WHEN '1' THEN 'x' ELSE NULL) AS program1, (CASE   program WHEN '2' THEN 'x' ELSE NULL) AS program2, (CASE program WHEN '3' THEN 'x' ELSE NULL) AS program3 FROM table GROUP BY ipiresia

Any help is appreciated.

Possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Strawberry, Nov 30 '15 at 11:03
Because in the example you gave there are three columns and the CASE is based on the 3rd column. I don't know if this is enough. — dimoss, Nov 30 '15 at 11:16

score 1 · Accepted Answer · answered Nov 30 '15 at 11:25

Seems sufficiently similar to me, but anyway...

SELECT ipiresia
     , MAX(CASE WHEN program = 1 THEN 'x' ELSE '' END) program1 
     , MAX(CASE WHEN program = 2 THEN 'x' ELSE '' END) program2 
     , MAX(CASE WHEN program = 3 THEN 'x' ELSE '' END) program3 
  FROM my_table 
 GROUP 
    BY ipiresia;

Personally, I wouldn't do this - preferring where possible to handle issues of data display in application-level code.

Convert rows to column using CASE in mysql

1 Answers1