October 4, 2011 at 9:40 am
Hi all,
I wondered if anyone might have an idea how to accomplice this scenario.
I have schools with bookings spread across 6 phases or terms, each booking may or may not be confirmed or non-existent.
Up until now I was producing a pivot table with a few extra columns required to make the returned data suitable, so I had one row per school with the first set of columns my extra data and then the final set of columns being the results for each phase - session confirmed/prov or no session.
This has worked great but now there is the possibility of multiple bookings per phase and I am at a loss as to how I might handle this in the pivot.
Example data structure below;
CREATE TABLE [dbo].[SESSIONS] (
[PROVISIONALID] [int] IDENTITY (1, 1) NOT NULL ,
[CONFIRMEDID] [int] NULL,
[DFES] [int] NOT NULL ,
[PHASE] [tinyint] NOT NULL ,
[PHASEYEAR] [int] NULL ,
[ACTIVITYNAME] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
[STARTDATE] [smalldatetime] NULL ,
[ENDDATE] [smalldatetime] NULL ,
) ON [PRIMARY]
GO
INSERT INTO SESSIONS (CONFIRMEDID, DFES, PHASE, PHASEYEAR, ACTIVITYNAME, STARTDATE, ENDDATE)
VALUES (NULL,1,1,2011,'Some stuff', '01/09/2011' , '01/12/2011')
;
INSERT INTO SESSIONS (CONFIRMEDID, DFES, PHASE, PHASEYEAR, ACTIVITYNAME, STARTDATE, ENDDATE)
VALUES (20,1,2,2011,'Some stuff', '01/09/2011' , '01/12/2011')
;
INSERT INTO SESSIONS (CONFIRMEDID, DFES, PHASE, PHASEYEAR, ACTIVITYNAME, STARTDATE, ENDDATE)
VALUES (15,1,1,2011,'Some stuff', '01/09/2011' , '01/12/2011')
;
INSERT INTO SESSIONS (CONFIRMEDID, DFES, PHASE, PHASEYEAR, ACTIVITYNAME, STARTDATE, ENDDATE)
VALUES (NULL,2,1,2011,'Some stuff', '01/09/2011' , '01/12/2011')
;
INSERT INTO SESSIONS (CONFIRMEDID, DFES, PHASE, PHASEYEAR, ACTIVITYNAME, STARTDATE, ENDDATE)
VALUES (NULL,2,2,2011,'Some stuff', '01/09/2011' , '01/12/2011')
;
This isnt exactly what it looks like but its close enough. I think the user is expecting to see multiple rows for schools that have two bookings in one phase, or the column for that phase containing both the ACTIVITYNAMEs for that phase or even something like DOUBLE BOOKED.
I am creating the pivot using the oft cited examples found here where you first create the temp table with the pivot columns you want to grab and then build up a big case statement to execute against the non pivotted data.
Any ideas I'd love to hear them
October 4, 2011 at 12:56 pm
Without seeing the expected result based on your sample data it'S hard to tell what solution may be useful.
In general, I would look into the CrossTab approach as describerd in the related link in my signature.
October 5, 2011 at 2:22 am
Hi there,
Please find below an edited version with a code snippet that will bring up the results I am getting now.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable (
[PROVISIONALID] [int] IDENTITY (1, 1) NOT NULL ,
[CONFIRMEDID] [int] NULL,
[DFES] [int] NOT NULL ,
[PHASE] [tinyint] NOT NULL ,
[PHASEYEAR] [int] NULL ,
[ACTIVITYNAME] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
[STARTDATE] [smalldatetime] NULL ,
[ENDDATE] [smalldatetime] NULL ,
) ON [PRIMARY]
SET IDENTITY_INSERT #mytable ON
INSERT INTO #mytable (PROVISIONALID, CONFIRMEDID, DFES, PHASE, PHASEYEAR, ACTIVITYNAME, STARTDATE, ENDDATE)
SELECT 1,1,1,1,2011,'Football', '01/09/2011' , '01/12/2011' UNION ALL
SELECT 2,2,1,1,2011,'Baseball', '01/09/2011' , '01/12/2011' UNION ALL
SELECT 3,NULL,1,2,2011,'Hockey', '01/09/2011' , '01/12/2011' UNION ALL
SELECT 4,3,2,2,2011,'Football', '01/09/2011' , '01/12/2011' UNION ALL
SELECT 5,NULL,2,2,2011,'Rugby', '01/09/2011' , '01/12/2011' UNION ALL
SELECT 6,NULL,2,3,2011,'Football', '01/09/2011' , '01/12/2011' UNION ALL
SELECT 7,4,2,4,2011,'Martial Arts', '01/09/2011' , '01/12/2011' UNION ALL
SELECT 8,NULL,3,1,2011,'Football', '01/09/2011' , '01/12/2011' UNION ALL
SELECT 9,NULL,3,2,2011,'Dance', '01/09/2011' , '01/12/2011'
SET IDENTITY_INSERT #mytable OFF
--=== School DFES 1 has 3 bookings two of which are in the same phase (1)
--=== School DFES 2 has 4 bookings two of which are in the same phase (2)
--=== School DFES 3 has 2 bookings in different phases
--=== Current pivot query looks like this
select DFES,
MAX(CASE WHEN PHASE='1' THEN ACTIVITYNAME END) as [PHASE_1],
MAX(CASE WHEN PHASE='2' THEN ACTIVITYNAME END) as [PHASE_2],
MAX(CASE WHEN PHASE='3' THEN ACTIVITYNAME END) as [PHASE_3],
MAX(CASE WHEN PHASE='4' THEN ACTIVITYNAME END) as [PHASE_4],
MAX(CASE WHEN PHASE='5' THEN ACTIVITYNAME END) as [PHASE_5],
MAX(CASE WHEN PHASE='6' THEN ACTIVITYNAME END) as [PHASE_6]
from
(
SELECT
DFES, PHASE, CASE WHEN CONFIRMEDID IS NULL THEN 'PROVISIONAL ' + ACTIVITYNAME ELSE 'CONFIRMED ' + ACTIVITYNAME END AS ACTIVITYNAME
FROM #mytable WHERE PHASEYEAR = 2011
GROUP BY DFES, CONFIRMEDID, PROVISIONALID, PHASE, ACTIVITYNAME
)
A GROUP BY DFES
DROP TABLE #mytable
The schools or DFES' that have two bookings in a single phase only show a single booking, I'd like to get that extra info out somehow as an extra row for all schools with multiple bookings in a phase.
Thanks
October 5, 2011 at 1:38 pm
Ok, now we have the result set you don't want to have.
But what is the result set you're looking for?
October 6, 2011 at 12:43 am
select
DFES
, MAX(CASE WHEN PHASE='1' THEN ACTIVITYNAME END) as [PHASE_1]
, MAX(CASE WHEN PHASE='2' THEN ACTIVITYNAME END) as [PHASE_2]
, MAX(CASE WHEN PHASE='3' THEN ACTIVITYNAME END) as [PHASE_3]
, MAX(CASE WHEN PHASE='4' THEN ACTIVITYNAME END) as [PHASE_4]
, MAX(CASE WHEN PHASE='5' THEN ACTIVITYNAME END) as [PHASE_5]
, MAX(CASE WHEN PHASE='6' THEN ACTIVITYNAME END) as [PHASE_6]
from
(
SELECT
m1.DFES
, m1.PHASE
, CASE WHEN m1.CONFIRMEDID IS NULL THEN 'PROVISIONAL ' ELSE 'CONFIRMED ' END + m1.ACTIVITYNAME AS ACTIVITYNAME
, COUNT(m2.PROVISIONALID) RN
FROM
#mytable m1
INNER JOIN #mytable m2 ON m2.DFES = m1.DFES AND m2.PHASE = m1.PHASE AND m2.PHASEYEAR = m1.PHASEYEAR AND m2.PROVISIONALID <= m1.PROVISIONALID
WHERE
m1.PHASEYEAR = 2011
GROUP BY
m1.DFES, m1.CONFIRMEDID, m1.PROVISIONALID, m1.PHASE, m1.ACTIVITYNAME)A
GROUP BY
DFES, rn
ORDER BY
DFES, rn
?
I Have Nine Lives You Have One Only
THINK!
October 7, 2011 at 8:22 am
Thank you, that sort of does the trick..you can control the row output so you might have three rows where they could fit on two but thats front end stuff I think.
I would not have thought of this so thank you very much.
Rolf
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply