2

I am trying to structure a query for a calendar view.

I want each row to return the details for a person and what they are doing for 7 days (split into AM and PM)

So the result set might look like this:

Name - Day1AM - Day1PM - Day2AM - Day2PM (...more days across the columns)
"John Smith" Event1 Event1 <blank> <blank> 
"Bill Jones" <blank> <blank> Event1 Event1 

Each day section could have no data:

EDITED to add scripts:

DDL Script and Data:

CREATE DATABASE [TestPivot] ON  PRIMARY 
( NAME = N'TestPivot', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestPivot.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestPivot_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestPivot_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Use [TestPivot]
Go

CREATE TABLE [dbo].[Person](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,

 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Event](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] 


CREATE TABLE [dbo].[DayPart](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [EventID] [int] NOT NULL,
    [Date] [datetime] NOT NULL,
 CONSTRAINT [PK_DayPart] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[DayPart]  WITH CHECK ADD  CONSTRAINT [FK_DayPart_Event] FOREIGN KEY([EventID])
REFERENCES [dbo].[Event] ([ID])
GO

ALTER TABLE [dbo].[DayPart] CHECK CONSTRAINT [FK_DayPart_Event]
GO

GO

CREATE TABLE [dbo].[PersonDayPart](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PersonID] [int] NOT NULL,
    [DayPartID] [int] NOT NULL, 
 CONSTRAINT [PK_PersonDayPart] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PersonDayPart]  WITH CHECK ADD  CONSTRAINT [FK_PersonDayPart_DayPart] FOREIGN KEY([DayPartID])
REFERENCES [dbo].[DayPart] ([ID])
GO

ALTER TABLE [dbo].[PersonDayPart] CHECK CONSTRAINT [FK_PersonDayPart_DayPart]
GO

ALTER TABLE [dbo].[PersonDayPart]  WITH CHECK ADD  CONSTRAINT [FK_PersonDayPart_Person] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Person] ([ID])
GO

ALTER TABLE [dbo].[PersonDayPart] CHECK CONSTRAINT [FK_PersonDayPart_Person]

--person data
INSERT INTO [Person] ([FirstName] ,[LastName])
VALUES ('John' , 'Smith')
INSERT INTO [Person] ([FirstName] ,[LastName])
VALUES ('Jean' , 'Jones')

--event data
INSERT INTO [Event] ([Title])
VALUES('Course 1' )
INSERT INTO [Event] ([Title])
VALUES('Course 2' )

-- day part data
DECLARE  @dayPart1ID int, @dayPart2ID int, @dayPart3ID int, @dayPart4ID int
INSERT INTO [DayPart] ([EventID],[Date])
VALUES (1, '1/1/2012')
select @dayPart1ID = @@identity
INSERT INTO [DayPart] ([EventID],[Date])
VALUES (1, '2/1/2012')
select @dayPart2ID = @@identity
INSERT INTO [DayPart] ([EventID],[Date])
VALUES (1, '3/1/2012')
select @dayPart3ID = @@identity
INSERT INTO [DayPart] ([EventID],[Date])
VALUES (1, '3/1/2012')
select @dayPart4ID = @@identity

--Person Day Parts for @dayPart1ID
INSERT INTO [PersonDayPart] ([PersonID],[DayPartID])
VALUES (1,@dayPart1ID)
INSERT INTO [PersonDayPart] ([PersonID],[DayPartID])
VALUES (2,@dayPart1ID)


--Person Day Parts for @dayPart2ID
INSERT INTO [PersonDayPart] ([PersonID],[DayPartID])
VALUES (2,@dayPart2ID)


--Person Day Parts for @dayPart3ID
INSERT INTO [PersonDayPart] ([PersonID],[DayPartID])
VALUES (2,@dayPart3ID)
INSERT INTO [PersonDayPart] ([PersonID],[DayPartID])
VALUES (1,@dayPart3ID)


--Person Day Parts for @dayPart4ID
INSERT INTO [PersonDayPart] ([PersonID],[DayPartID])
VALUES (2,@dayPart4ID)
INSERT INTO [PersonDayPart] ([PersonID],[DayPartID])
VALUES (1,@dayPart4ID)

Ideally I could pass the query a start date and it would use that as the basis for the 7 days.

I am aware of Pivot and using case statement methods, but have really struggled trying to piece this together.

Edit: Here is my attempt at a pivot, it seems to work ok:

select * 
from 
( 
    select firstname,[Date], e.Title as stringvalue 
    from    
            dbo.DayPart dp
         LEFT outer JOIN
            dbo.PersonDayPart idp ON dp.ID = idp.DayPartID  
        left outer JOIN
            dbo.Person p ON idp.PersonID = p.ID 
              LEFT outer JOIN
        dbo.Event e ON e.ID = idp.DayPartID
) as d 
pivot 
( 
    min([stringvalue]) 
    for [Date] in ([2012-01-01], [2012-02-01], [2012-03-01], [2012-04-01], [2012-05-01], [2012-06-01],[2012-07-01]) 
) 
as p 

What I would like to know is if it is possible to set the dates in the FOR section dynamically in some fashion based on a start date passed into a stored procedure.

  • Can you show some sample data and desired results? – Aaron Bertrand May 31 '12 at 11:54
  • @AaronBertrand - thanks Aaron. I've added some test data to the question and updated the resultset – ozz May 31 '12 at 12:13
  • 1
    @Ozz Example/sample should be DDL scripts for your schema plus insert scripts for test data please. – Mark Storey-Smith May 31 '12 at 12:22
  • @MarkStorey-Smith my bad, first Q on this site.... will upload shortly – ozz May 31 '12 at 12:30
  • 1
    All your audience on this site, where you are asking for help? – Aaron Bertrand May 31 '12 at 13:54
4

SQL Server doesn't have dynamic or variable-based pivot, so one approach could be dynamic SQL.

DECLARE @StartDate DATE = '20120101';

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT TOP (7) 
    @sql += N',[' + CONVERT(CHAR(10), DATEADD(DAY, ROW_NUMBER() OVER 
      (ORDER BY [object_id])-1, @StartDate), 120) + ']'
    FROM sys.all_objects ORDER BY [object_id];

SELECT @sql = N'select * 
from 
( 
    select firstname,[Date], dp.ID as stringvalue 
    from    
            dbo.DayPart dp
         LEFT outer JOIN
            dbo.PersonDayPart idp ON dp.ID = idp.DayPartID  
        left outer JOIN
            dbo.Person p ON idp.PersonID = p.ID 
) as d 
pivot 
( 
  min([stringvalue]) for [Date] in 
  (' + STUFF(@sql, 1, 1, '') + ') 
) 
as p;';

PRINT @sql;
-- EXEC sp_executesql @sql;

Result:

select * 
from 
( 
    select firstname,[Date], dp.ID as stringvalue 
    from    
            dbo.DayPart dp
         LEFT outer JOIN
            dbo.PersonDayPart idp ON dp.ID = idp.DayPartID  
        left outer JOIN
            dbo.Person p ON idp.PersonID = p.ID 
) as d 
pivot 
( 
  min([stringvalue]) for [Date] in 
  ([2012-01-01],[2012-01-02],[2012-01-03],[2012-01-04],[2012-01-05],[2012-01-06],[2012-01-07]) 
) 
as p;

Output when executed:

firstname 2012-01-01  2012-01-02  2012-01-03  2012-01-04  2012-01-05  2012-01-06  2012-01-07
--------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Jean      1           2           3           NULL        NULL        NULL        NULL
John      1           NULL        3           NULL        NULL        NULL        NULL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.