Displaying rows as Columns in table

mysql, select, sql

Solution

SELECT Period,
        MAX(CASE WHEN Location = 'Location A' THEN Rate_per_SqFt ELSE NULL END) `Location A`,
        MAX(CASE WHEN Location = 'Location B' THEN Rate_per_SqFt ELSE NULL END) `Location B`,
        MAX(CASE WHEN Location = 'Location C' THEN Rate_per_SqFt ELSE NULL END) `Location C`
FROM statistics
GROUP BY Period

- SQLFiddle Demo Link

if you have unknown number of locations, a Dynamic SQL is preferred

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN Location = ''',
      Location,
      ''' then Rate_per_SqFt ELSE NULL end) AS ',
      CONCAT('`',Location,'`')
    )
  ) INTO @sql
FROM statistics;

SET @sql = CONCAT('SELECT Period, ', @sql, ' 
                   FROM Statistics 
                   GROUP BY Period');

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

- SQL Fiddle Demo Link

Problem

I have a Table Like Below ``` CREATE TABLE Statistics(Stat_Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Period VARCHAR(55), Location VARCHAR(255), Rate_per_SqFt INT) ``` The Datas in the Table are ``` INSERT INTO Statistics(Period, Location, Rate_per_SqFt) VALUES('June', 'Location A', 2500), ('June', 'Location B', 2740), ('June', 'Location C', 3200), ('July', 'Location A', 2650), ('July', 'Location B', 2800), ('July', 'Location C', 3250), ('August', 'Location A', 2750), ('August', 'Location B', 2950), ('August', 'Location C', 3230), ('October', 'Location A', 2950), ('October', 'Location B', 3950), ('October', 'Location C', 3530); ``` I want the Rows for Particular Month Displayed in Output as Separate Columns as Below ``` Period Location A Location B Location C June 2500 2740 3200 July 2650 2800 3250 August 2750 2950 3230 October 2950 3950 3530 ``` How can I do this using Query

Original source