CODEWITHSUNDEEP
mysqlpivotcase
dimoss
dimoss

Reputation: 489

Convert rows to column using CASE in mysql

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.

Upvotes: 1

Views: 2291

Answers (1)

Strawberry
Strawberry

Reputation: 33935

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.

Upvotes: 1

Related Questions