Geek Answers Handbook

Display rows as columns in a table

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)

Data in the table

 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 a specific month to be displayed as output in separate columns 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 to do it with Query

+4
source share
2 answers
 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

if you have an unknown number of places, it is recommended to use dynamic SQL

 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;
+4
source

here you can see 5 methods : convert rows to a column (the generalized form of the question you asked):

+1
source

More articles:


All Articles