Pivoting a table

• Debbie Edwards

  SSCrazy Eights

  Points: 9217

  More actions

  October 13, 2010 at 5:11 am

  #241708

  First of all I create my data

  I have established that pupils can have up to 10 attendances

  CREATE TABLE #Test1(

  Pupil_ID [int] NOT NULL,

  AttendanceID [int] NULL,

  ATT_PAT_FROM_DATE dateTime NULL,

  ATT_PAT_TO_DATE dateTime NULL)

  INSERT INTO #Test1

  SELECT 52023,248765,'2006-09-04 00:00:00.000','2008-04-04 00:00:00.000' UNION

  SELECT 52023,256298,'2008-03-10 00:00:00.000','2008-03-28 00:00:00.000' UNION

  SELECT 52023,240827,'2008-03-11 00:00:00.000','2008-03-31 00:00:00.000' UNION

  SELECT 52023,319786,'2008-03-31 00:00:00.000','2008-04-25 00:00:00.000' UNION

  SELECT 52023,316993,'2008-04-28 00:00:00.000','2008-08-31 00:00:00.000' UNION

  SELECT 52023,448510,'2008-09-08 00:00:00.000','2008-10-07 00:00:00.000' UNION

  SELECT 52023,312765,'2008-10-08 00:00:00.000','2009-02-13 00:00:00.000' UNION

  SELECT 52023,271174,'2009-02-23 00:00:00.000','2009-07-24 00:00:00.000' UNION

  SELECT 52023,448261,'2009-09-01 00:00:00.000',NULL

  I want a query to change this data (Earliest Attendance date first)

  SELECT * FROM #Test1

  ORDER BY ATT_PAT_FROM_DATE asc

  (pivot) so you get.....

  Pupil_ID, AttID1 AttID2, AttID3, AttID4, AttID5, AttID6, AttID7, AttID8, AttID9, AttID10

  52023,248765,256298,240827, 319786, 316993,448510, 312765, 271174, 448261, NULL

  Obviously there will be more pupils in the dataset.

  Can anyone help me with this one?

  Im imagining PIVOT is used?

  Thanks

  Debbie

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  October 13, 2010 at 5:21 am

  #1236082

  WITH CTE AS (

  SELECT Pupil_ID,

  AttendanceID,

  ROW_NUMBER() OVER(PARTITION BY Pupil_ID ORDER BY ATT_PAT_FROM_DATE,ATT_PAT_TO_DATE,AttendanceID) AS rn

  FROM #Test1)

  SELECT Pupil_ID,

  MAX(CASE WHEN rn=1 THEN AttendanceID END) AS AttID1,

  MAX(CASE WHEN rn=2 THEN AttendanceID END) AS AttID2,

  MAX(CASE WHEN rn=3 THEN AttendanceID END) AS AttID3,

  MAX(CASE WHEN rn=4 THEN AttendanceID END) AS AttID4,

  MAX(CASE WHEN rn=5 THEN AttendanceID END) AS AttID5,

  MAX(CASE WHEN rn=6 THEN AttendanceID END) AS AttID6,

  MAX(CASE WHEN rn=7 THEN AttendanceID END) AS AttID7,

  MAX(CASE WHEN rn=8 THEN AttendanceID END) AS AttID8,

  MAX(CASE WHEN rn=9 THEN AttendanceID END) AS AttID9,

  MAX(CASE WHEN rn=10 THEN AttendanceID END) AS AttID10

  FROM CTE

  GROUP BY Pupil_ID

  ORDER BY Pupil_ID;

  ____________________________________________________

  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

• Debbie Edwards

  SSCrazy Eights

  Points: 9217

  More actions

  October 13, 2010 at 5:27 am

  #1236084

  Who hoo it worked. Ive never used With before so Im going to try and work everything out.

  Thanks for that 🙂

  Debbie