CODEWITHSUNDEEP
mysqlsqlpivot
Ray
Ray

Reputation: 2728

SQL Create Columns From values and fill with other column

I want to take values from 1 column, and turn them into headers for another. Its easier to show you:

Initial Table:

Name      | Label   | Value
Tom Jones | January | 42
Rick James| March   | 83

Ending Table:

Name      | January | March
Tom Jones | 42      | NULL
Rick James| NULL    | 83

The format is needed like this because of the type of sorting and ordering that I am doing on the Label.

UPDATE: When I mention multiple columns for value I mean something like:

Name      | Label_1   | ValueA_1 | ValueB_1 | Label_2 | ValueA_2 | ValueB_2
Tom Jones | January   | 42       | 77       | March   | 99       | 22
Rick James| March     | 83       | 17       | May     | NULL     | 112

Upvotes: 3

Views: 12019

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79959

Use CREATE TABLE NewTable AS SELECT ...:

CREATE TABLE newTable
SELECT
  NAme, 
  SUM(CASE WHEN Label = 'January' THEN value  END) AS January,
  SUM(CASE WHEN Label = 'March' THEN value END) AS March
FROM tablename AS t1
GROUP BY Name;

This will create a brand new table newTable with the same structure of the SELECT statement.

Update:

Based on your comment, if you want to get the values of value comma separated:

CREATE TABLE newTable
SELECT
  Name, 
  GROUP_CONCAT(CASE WHEN Label = 'January' THEN value  END) AS January,
  GROUP_CONCAT(CASE WHEN Label = 'March' THEN value  END) AS March
FROM table1 AS t1
GROUP BY Name;

Update:

If you want to do this dynamically, not hardcoding the values, you have to use dynamic sql to do so, like this:

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT CONCAT('GROUP_CONCAT(IF(Label = ''',
      Label, ''', value, NULL)) AS ', '''', Label , '''')
  ) INTO @sql
FROM Table1 ;

SET @sql = CONCAT('CREATE TABLE newTable ',
                  'SELECT Name,' ,
                  @sql, ' FROM table1 ',
                  ' GROUP BY Name');


prepare stmt 
FROM @sql;

execute stmt;

Updated SQL Fiddle Demo

Upvotes: 2

Related Questions