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.

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
– dimoss, Commented Nov 30, 2015 at 11:16

Strawberry · Accepted Answer · 2015-11-30 11:25:23Z

1

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.

answered Nov 30, 2015 at 11:25

Strawberry

34k14 gold badges43 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert rows to column using CASE in mysql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related