Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
show 5 more comments

1 Answer

up vote 4 down vote accepted

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
share|improve this answer
    
thanks Aaron, this is just what I'm looking for. –  Ozz May 31 '12 at 13:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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