October 21, 2010 at 9:17 am
Arrrrghhhhh Im having a nightmare.
I have created some test data
CREATE TABLE [dbo].[#Test](
[Attendance_Type] Varchar(100) NULL,
[Systalk_ID] [int] NULL,
Other_Attendance_ID [int] NULL ,
Att_PAT_FROM_DATE datetime,
Att_PAT_TO_DATE datetime,
SC_Attendance_ID[int] NULL,
SCH_DFEE [int] NULL)
INSERT INTO #Test
SELECT 'Attendance details in 12 month period',161,320715,'2005-02-28 00:00:00.000','2005-08-31 00:00:00.000',305657,1109 UNION
SELECT 'Attendance details in 12 month period',161,447843,'2005-09-05 00:00:00.000','2007-06-29 00:00:00.000',305657,1109 UNION
SELECT 'Attendance details in 12 month period',161,447843,'2005-09-05 00:00:00.000','2007-06-29 00:00:00.000',320715,1110 UNION
SELECT 'Attendance details in 12 month period',9887,236434,'2006-04-24 00:00:00.000','2006-08-31 00:00:00.000',355529,1101 UNION
SELECT 'Attendance details in 12 month period',9887,349504,'2006-09-05 00:00:00.000','2007-08-31 00:00:00.000',236434,1102 UNION
SELECT 'Attendance details in 12 month period',9887,349504,'2006-09-05 00:00:00.000','2007-08-31 00:00:00.000',355529,1101 UNION
SELECT 'Attendance details in 12 month period',9887,204244,'2009-10-02 00:00:00.000',NULL,448656,1111
SELECT * FROM #Test
--Create a temporary table CTE
WITH CTE AS (
SELECT Systalk_ID,Attendance_Type,
Other_Attendance_ID,SC_Attendance_ID,
ROW_NUMBER() OVER(PARTITION BY Systalk_ID ORDER BY ATT_PAT_FROM_DATE,ATT_PAT_TO_DATE,Other_Attendance_ID) AS rn
FROM #Test
)
SELECT Systalk_ID,Attendance_Type,SC_Attendance_ID,
MAX(CASE WHEN rn=1 THEN Other_Attendance_ID END) AS AttID1,
MAX(CASE WHEN rn=2 THEN Other_Attendance_ID END) AS AttID2,
MAX(CASE WHEN rn=3 THEN Other_Attendance_ID END) AS AttID3,
MAX(CASE WHEN rn=4 THEN Other_Attendance_ID END) AS AttID4,
MAX(CASE WHEN rn=5 THEN Other_Attendance_ID END) AS AttID5,
MAX(CASE WHEN rn=6 THEN Other_Attendance_ID END) AS AttID6,
MAX(CASE WHEN rn=7 THEN Other_Attendance_ID END) AS AttID7,
MAX(CASE WHEN rn=8 THEN Other_Attendance_ID END) AS AttID8,
MAX(CASE WHEN rn=9 THEN Other_Attendance_ID END) AS AttID9,
MAX(CASE WHEN rn=10 THEN Other_Attendance_ID END) AS AttID10
--INTO #Pupils_Attendance_Matrix
FROM CTE
GROUP BY Systalk_ID,Attendance_Type,SC_Attendance_ID
ORDER BY Systalk_ID
When I have finished all that I want all my IDS to be in the first column onwards like...
systalk_ID, SC_Attendance_ID AttID1 AttID1 AttID1 AttID1 AttID1 AttID1 AttID1 AttID1 AttID1 AttID1
161 305657320715447843NULLNULLNULLNULLNULLNULLNULLNULL
161 320715447843NULLNULLNULLNULLNULLNULLNULLNULLNULL
9887 236434349504NULLNULLNULLNULLNULLNULLNULLNULLNULL
9887 355529236434349504NULLNULLNULLNULLNULLNULLNULLNULL
9887 448656204244NULLNULLNULLNULLNULLNULLNULLNULLNULL
Each Pupil can have numberous SC attendances
However it comes out like
systalk_ID, SC_Attendance_ID AttID1 AttID2 AttID3 AttID4 AttID5 AttID6 AttID7 AttID8 AttID9 AttID10
161305657320715447843NULLNULLNULLNULLNULLNULLNULLNULL
161320715NULLNULL447843NULLNULLNULLNULLNULLNULLNULL
9887236434NULL349504NULLNULLNULLNULLNULLNULLNULLNULL
9887355529236434NULL349504NULLNULLNULLNULLNULLNULLNULL
9887448656NULLNULLNULL204244NULLNULLNULLNULLNULLNULL
can any one see what I have done wrong????
Debbie
October 21, 2010 at 10:39 am
Your row number is only partitioned by the Systalk_ID, but the grouping in the outer query is based on Systalk_ID, Attendance_Type, and SC_Attendance_ID. You want these to match.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 22, 2010 at 3:29 am
OF COURSE!:doze:
Fantastic, Everything is in the right place. Thank you so much
Debbie
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply