SQL query with dynamic columns (PIVOT)

Question

I am trying to write a SQL query that will turn this table:

Start_time    End_time   Instructor    Student  
9:00          9:35       Joe Bob       Andrew
9:00          9:35       Joe Bob       Smith
9:00          9:35       Roberto       Andy
10:00         10:35      Joe Bob       Angelica
11:00         11:20      Roberto       Bob

Into something like this:

Instructor    9:00              10:00         11:00
Joe Bob       Andrew, Smith     Angelica      NULL
Roberto       Andy              NULL          Bob

I think that this is some sort of PIVOT command but I am not sure how I should go about writing the SQL query. The times are all dynamically generated so I would prefer it if the query would generate the column names in the second table dynamically (for example, if the original table contained an additional start time of 11:30, there should be a new column for 11:30 in the result).

Thank you in advance, I've been playing with this for a while but couldn't get it to work on my own. I can provide the SQL INSERT commands to give you the full data if necessary.

EDIT: It would be particularly helpful to get the result of this select statement as a VIEW. Thanks!

EDIT 2: The code that is generating the view that makes the first table is:

CREATE VIEW schedule_view AS SELECT RTRIM(SUBSTRING(students.schedule_first_choice, 1, 5)) AS start_time, RTRIM(SUBSTRING(students.schedule_first_choice, -10, 5) AS end_time, CONCAT(instructors.first_name, ' ', instructors.last_name) AS instructor_name, 
    CONCAT(students.first_name, ' ', students.last_name) AS student_name , students.swim_america_level as class 
    FROM students, instructors WHERE students.instructor_id = instructors.instructor_id AND students.season = 
    (SELECT constant_value FROM constants WHERE constant_name = 'season') AND students.year = 
    (SELECT constant_value FROM constants WHERE constant_name = 'year')

See http://stackoverflow.com/questions/7674786/mysql-pivot-table — ic3b3rg, Dec 30 '12 at 04:18

score 2 · Accepted Answer · answered Dec 30 '12 at 04:19

2

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT(case when Start_time = ''',
      Start_time,
      ''' then Student ELSE NULL end) AS ',
      CONCAT('`',Start_time,'`')
    )
  ) INTO @sql
FROM Table1;

SET @sql = CONCAT('SELECT Instructor, ', @sql, ' 
                   FROM Table1 
                   GROUP BY Instructor');

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

SQLFiddle Demo

answered Dec 30 '12 at 04:19

John Woo

258,903
69
498
492

Ok, that's great, thank you. It works perfectly in SQLFiddle. However, when I run it in my database I get this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1 . I'm running MySQL 5.5 -- any suggestions? Thanks! – user1886266 Dec 30 '12 at 04:36
can you post the query you've run? i gues you are missing something causing to have error on your syntax `:D` – John Woo Dec 30 '12 at 04:37
I simply changed the column names you used to mine (I am selecting from a view, I hope that's not a problem): SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'GROUP_CONCAT(case when start_time = ''', start_time, ''' then student_name ELSE NULL end) AS ', CONCAT('`',start_time,'`'))) INTO @sql FROM schedule_view; SET @sql = CONCAT('SELECT instructor_name, ', @sql, ' FROM schedule_view GROUP BY instructor_name'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; – user1886266 Dec 30 '12 at 04:43
can you put backtick around the column name CONCAT('\`',start_time,'\`') ? – John Woo Dec 30 '12 at 04:46
Oh sorry, the backticks didn't show up in the comment I posted, but they are included in the query I ran on the server. – user1886266 Dec 30 '12 at 04:51
are you running this inside a stored procedure? – John Woo Dec 30 '12 at 04:53
I am not. Should I? Not sure how to do that in phpmyadmin. – user1886266 Dec 30 '12 at 05:00
Ok, figured out how to run stored procedures and so on. When I run your test data I get the appropriate output within phpmyadmin. However, when I run it on my view I am still getting the syntax error. The code that generates my view is appended to my question above. Thanks again! – user1886266 Dec 30 '12 at 22:50

Bharani · Answer 2 · 2013-01-24T14:07:59.163

IN THESE ALL QUERY EXECUTED AND TESTED IN SQL SEREVR

USING STATIC COLUMNS IN PIVOT

 Select * from  
 (
      select Instructor,Start_time, STUFF((select ',' + student from Table1 a where         
      a.Start_time = b.Start_time and
      a.Instructor=b.Instructor for xml     path('')),1,1,'') as student
      from table1 b ) x 
 PIVOT 
 (
      max(Student) 
      for start_time IN ([9:00],[10:00], [11:00])
 ) p

DYNAMICALLY CREATE PIVOT TABLE Using Temp Table

 Declare @tab nvarchar(max) 
 Declare @pivottab nvarchar(max)

 Create table #Table2 (     
        instructor varchar(100),    
        student    varchar(100),    
        start_time varchar(10) 
 ); 

 insert into #Table2 (instructor,student,start_time) 
 select 
       Instructor,  
       STUFF((Select ','+student 
             from Table1 as a
         Where 
             a.Start_time = b.Start_time and 
                 a.Instructor=b.Instructor
             for xml path('')),1,1,''),
       Start_time  
 from table1 as b

 select @tab = coalesce(@tab + ',['+start_time+']' ,'['+start_time+']')  
 from #Table2  
 group by Start_time

 set @pivottab = 
     N' select * from  (
                        select Instructor, Start_time, student from #Table2 
                       ) x 
        PIVOT (
              max(Student) for start_time IN ('+@tab+') 
        ) p'

 execute(@pivottab)

 if exists(select * from #table2)
 Begin
Drop table #table2 
 End

DYNAMICALLY CREATE PIVOT TABLE

Declare @tab nvarchar(max)
Declare @pivottab nvarchar(max)

Select @tab = coalesce(@tab + ',['+start_time+']' , '['+start_time+']') from Table1
group by Start_time

set @pivottab = N'
select *
from 
(
  select Instructor,Start_time,STUFF((select '+ char(39)+char(44)+char(39) + '+ a.student from Table1 as a
where a.Start_time = b.Start_time and a.Instructor=b.Instructor for xml path('''')),1,1,'''') 
as Student from table1 as b

) x
PIVOT
(
  max(Student)
  for start_time IN ('+@tab+')
) p'

execute(@pivottab)

This is a MySQL question so I am not sure why you are posting a SQL Server answer. — wickedone, Jan 24 '13 at 14:14
"I think that this is some sort of PIVOT command but I am not sure how I should go about writing the SQL query. The times are all dynamically generated so I would prefer it if the query would generate the column names in the second table dynamically (for example, if the original table contained an additional start time of 11:30, there should be a new column for 11:30 in the result)." They have doubt to how to dynamically create pivot table only then i build query to post here — Bharani, Jan 25 '13 at 06:20

SQL query with dynamic columns (PIVOT)

2 Answers2