converting multiple rows to columns in a table

Question

I have a table which contains the following rows :

╔══════════╦═══════════╦════════╗
║ PK_VALUE ║ RATING_ID ║ RATING ║
╠══════════╬═══════════╬════════╣
║       11 ║         1 ║     90 ║
║       11 ║         2 ║     80 ║
║       11 ║         3 ║     90 ║
║       12 ║         1 ║     90 ║
║       12 ║         2 ║     80 ║
║       12 ║         3 ║     90 ║
╚══════════╩═══════════╩════════╝

I want the above table as :

╔══════════╦════════════╦════════════╦════════════╗
║ PK_VALUE ║ RATING_ID1 ║ RATING_ID2 ║ RATING_ID3 ║
╠══════════╬════════════╬════════════╬════════════╣
║       11 ║         90 ║         80 ║         90 ║
║       12 ║         90 ║         80 ║         90 ║
╚══════════╩════════════╩════════════╩════════════╝

I tried some of the suggestions I found within a Google search, but it shows we have to insert into a table. I am not sure about this. Any suggestions welcome...

I have uploaded the screenshots related to my doubt.. image of the result query before what i use to get..

In this image we can see two review ID's for the same pk_value.. But I am receiving only one review ID after including your query.. result after including your query..

In the above How can I get result of both the review ID's???

You've tried googling but have you tried searching specifically this site? — Andriy M
– Andriy M, Commented Jan 22, 2013 at 9:27
Take a look at these links: Dynamic pivot tables, Automate pivot table queries. — Devart
– Devart, Commented Jan 22, 2013 at 9:29

John Woo · Accepted Answer · 2013-01-22 09:21:31Z

3

use CASE and MAX

SELECT  pk_value,
        MAX(CASE WHEN rating_ID = 1 THEN rating ELSE NULL END) AS r1,
        MAX(CASE WHEN rating_ID = 2 THEN rating ELSE NULL END) AS r2,
        MAX(CASE WHEN rating_ID = 3 THEN rating ELSE NULL END) AS r3
FROM    tableName
GROUP   BY pk_value

SQLFiddle Demo

Otherwise, if you have unknown number of Rating_ID and you don't want to constantly updated the query, a Dynamic SQL is recommended,

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN rating_ID = ',
      rating_id,
      ' then rating ELSE NULL end) AS `rating_id', rating_id, '`')
  ) INTO @sql
FROM TableName;

SET @sql = CONCAT('SELECT  pk_value, ', @sql, ' 
                   FROM    tableName
                   GROUP   BY pk_value');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

both the query above have the same result,

╔══════════╦════════════╦════════════╦════════════╗
║ PK_VALUE ║ RATING_ID1 ║ RATING_ID2 ║ RATING_ID3 ║
╠══════════╬════════════╬════════════╬════════════╣
║       11 ║         90 ║         80 ║         90 ║
║       12 ║         90 ║         80 ║         90 ║
╚══════════╩════════════╩════════════╩════════════╝

edited Jan 22, 2013 at 9:21

answered Jan 22, 2013 at 9:06

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

7 Comments

Pavan Kumar Over a year ago

Thank u sir... upto now I havent known about this SQL Fiddle.. this is really beautiful for developers.. Thank u once again..

Pavan Kumar Over a year ago

You have edited my question and added table structure to the numbers.. I would like to know how can we do that??

Pavan Kumar Over a year ago

sir a problem.. It is returning only one row of a table. if we have two or more pk values it is not displaing rest of them..

John Woo Over a year ago

@PavanKumar give mi some sample data :D so we can fix it.

John Woo Over a year ago

the original query was grouping with pk_value. try GROUP BY pk_value,review_id

|

Collectives™ on Stack Overflow

converting multiple rows to columns in a table

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related