Pivoting Problem

• gaurav-404321

  SSCommitted

  Points: 1593

  More actions

  September 1, 2010 at 4:37 am

  #239797

  I have a data in the following format:

  COURSECODE TYPE TIME

  A L 10-11 AM

  B L 11-12 AM

  B P 12-01 PM

  B P 01-02 PM

  B T 03-04 PM

  C L 04-05 PM

  D L NULL

  Following is the script for this table and data:

  create table coursedetails (coursecode varchar(5),attendancetype varchar(1),attendancetime varchar(10))

  insert into coursedetails values ('A','L','10-11 AM')

  insert into coursedetails values ('B','L','11-12 AM')

  insert into coursedetails values ('B','P','12-01 AM')

  insert into coursedetails values ('B','P','01-02 PM')

  insert into coursedetails values ('B','T','03-04 PM')

  insert into coursedetails values ('C','L','04-05 PM')

  insert into coursedetails values ('D','P','05-06 PM')

  I want a query to return the result in following manner shown in attached excel file

  please help in writing the query

  Thanks in advance

• Mark Cowne

  One Orange Chip

  Points: 26968

  More actions

  September 1, 2010 at 7:36 am

  #1214951

  SELECT coursecode,

  MAX(CASE WHEN attendancetype='L' THEN attendancetime END) AS L,

  MAX(CASE WHEN attendancetype='T' THEN attendancetime END) AS T,

  MAX(CASE WHEN attendancetype='P' THEN attendancetime END) AS P,

  MAX(CASE WHEN attendancetype='L' THEN 1 ELSE 0 END) AS [Is L],

  MAX(CASE WHEN attendancetype='T' THEN 1 ELSE 0 END) AS [Is T],

  MAX(CASE WHEN attendancetype='P' THEN 1 ELSE 0 END) AS [Is P]

  FROM coursedetails

  GROUP BY coursecode

  ORDER BY coursecode;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537