April 2, 2012 at 8:53 am
I have been asked to take our project data (approximately 40 records) and create a "pivot like" report showing months as column headers, Resources as row headers and project names in the cells. The purpose of the report is to identify "who" is responsible for "what" each month. The report will be exported to an Excel worksheet where I can deal with carriage returns to align project names.
--Essential Project Columns
CREATE TABLE [dbo].[Projects](
[Project] [VARCHAR](255) NULL,
[Owner] [VARCHAR](36) NULL,
[Sponsor] [VARCHAR](36) NULL,
[StartDate] [DATE] NULL,
[DueDate] [DATE] NULL,
[Resources] [VARCHAR](255) NULL,
) ON [PRIMARY]
--Generic Data (Multiple Resources delimited with semi colons)
INSERTINTO Projects (Project, [Owner], Sponsor, StartDate, DueDate, Resources)
VALUES ('Proj1', 'Own1', 'Spon1','4/1/12', '7/31/12', 'Res1; Res2'),
('Proj2', 'Own2', 'Spon1','5/1/12', '9/30/12', 'Res2; Res3; Res4'),
('Proj3', 'Own3', 'Spon2','4/1/12', '8/31/12', 'Res1; Res3; Res4'),
('Proj4', 'Own3', 'Spon2','6/1/12', '7/31/12', 'Res3; Res4; Res5'),
('Proj5', 'Own4', 'Spon1','4/15/12', '6/15/12', 'Res1; Res6; Res7'),
('Proj6', 'Own4', 'Spon1','5/15/12', '9/15/12', 'Res2; Res4; Res5; Res6; Res7')
--Typical output (could properly represent grid in this posting so I will describe the contents)
Column headers show dates (4/1, 5/1 etc.)
Row headers show people (Own?, Spon?, Res?)
Cells contain one or more project names that the resource is responsible for.
For example:
Own3 will show Proj3 and Proj4 for months beginning 6/1 and 7/1
Spon1 wIll show Proj1, Proj2, Proj5 and Proj6 for months beginning 5/1 and 6/1
April 2, 2012 at 7:21 pm
Thanks for posting some reasonably consumable DDL!
Try this:
--Essential Project Columns
DECLARE @Projects TABLE(
[Project] [VARCHAR](255) NULL,
[Owner] [VARCHAR](36) NULL,
[Sponsor] [VARCHAR](36) NULL,
[StartDate] [DATETIME] NULL,
[DueDate] [DATETIME] NULL,
[Resources] [VARCHAR](255) NULL
)
--Generic Data (Multiple Resources delimited with semi colons)
INSERT INTO @Projects (Project, [Owner], Sponsor, StartDate, DueDate, Resources)
SELECT 'Proj1', 'Own1', 'Spon1','4/1/12', '7/31/12', 'Res1; Res2'
UNION ALL SELECT 'Proj2', 'Own2', 'Spon1','5/1/12', '9/30/12', 'Res2; Res3; Res4'
UNION ALL SELECT 'Proj3', 'Own3', 'Spon2','4/1/12', '8/31/12', 'Res1; Res3; Res4'
UNION ALL SELECT 'Proj4', 'Own3', 'Spon2','6/1/12', '7/31/12', 'Res3; Res4; Res5'
UNION ALL SELECT 'Proj5', 'Own4', 'Spon1','4/15/12', '6/15/12', 'Res1; Res6; Res7'
UNION ALL SELECT 'Proj6', 'Own4', 'Spon1','5/15/12', '9/15/12', 'Res2; Res4; Res5; Res6; Res7'
;WITH AllRes AS (
SELECT Project, StartDate, DATEADD(month, DATEDIFF(month, 0, DueDate), 0) As EndDate
,x.Resources
FROM @Projects
CROSS APPLY (SELECT LTRIM(strcol) FROM SplitString(';', Resources)) AS x(Resources)
UNION ALL
SELECT Project, StartDate, DATEADD(month, DATEDIFF(month, 0, DueDate), 0), [Owner]
FROM @Projects
UNION ALL
SELECT Project, StartDate, DATEADD(month, DATEDIFF(month, 0, DueDate), 0), Sponsor
FROM @Projects
),
Tally (n) AS (
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
),
List AS (
SELECT Project, Resources, [Mof2012]
FROM AllRes
CROSS APPLY (
SELECT n FROM Tally
WHERE n BETWEEN DATEPART(month, StartDate) and DATEPART(month,EndDate)) x([Mof2012])
)
SELECT Resources
,STUFF((SELECT ', ' + Project
FROM List l2
WHERE l2.Mof2012 = 4 and l1.Resources = l2.Resources
ORDER BY Project
FOR XML PATH('')), 1, 1, '') AS '4/1'
,STUFF((SELECT ', ' + Project
FROM List l2
WHERE l2.Mof2012 = 5 and l1.Resources = l2.Resources
ORDER BY Project
FOR XML PATH('')), 1, 1, '') AS '5/1'
,STUFF((SELECT ', ' + Project
FROM List l2
WHERE l2.Mof2012 = 6 and l1.Resources = l2.Resources
ORDER BY Project
FOR XML PATH('')), 1, 1, '') AS '6/1'
,STUFF((SELECT ', ' + Project
FROM List l2
WHERE l2.Mof2012 = 7 and l1.Resources = l2.Resources
ORDER BY Project
FOR XML PATH('')), 1, 1, '') AS '7/1'
,STUFF((SELECT ', ' + Project
FROM List l2
WHERE l2.Mof2012 = 8 and l1.Resources = l2.Resources
ORDER BY Project
FOR XML PATH('')), 1, 1, '') AS '8/1'
,STUFF((SELECT ', ' + Project
FROM List l2
WHERE l2.Mof2012 = 9 and l1.Resources = l2.Resources
ORDER BY Project
FOR XML PATH('')), 1, 1, '') AS '9/1'
FROM List l1
GROUP BY Resources
It produces this (sorry results don't line up well but I'm too lazy to make them):
Resources4/1 5/16/17/18/19/1
Own1 Proj1 Proj1 Proj1 Proj1NULLNULL
Own2 NULL Proj2 Proj2 Proj2 Proj2 Proj2
Own3 Proj3 Proj3 Proj3, Proj4 Proj3, Proj4 Proj3NULL
Own4 Proj5 Proj5, Proj6 Proj5, Proj6 Proj6 Proj6 Proj6
Res1 Proj1, Proj3, Proj5 Proj1, Proj3, Proj5 Proj1, Proj3, Proj5 Proj1, Proj3 Proj3NULL
Res2 Proj1 Proj1, Proj2, Proj6 Proj1, Proj2, Proj6 Proj1, Proj2, Proj6 Proj2, Proj6 Proj2, Proj6
Res3 Proj3 Proj2, Proj3 Proj2, Proj3, Proj4 Proj2, Proj3, Proj4 Proj2, Proj3 Proj2
Res4 Proj3 Proj2, Proj3, Proj6 Proj2, Proj3, Proj4, Proj6 Proj2, Proj3, Proj4, Proj6 Proj2, Proj3, Proj6 Proj2, Proj6
Res5 NULL Proj6 Proj4, Proj6 Proj4, Proj6 Proj6 Proj6
Res6 Proj5 Proj5, Proj6 Proj5, Proj6 Proj6 Proj6 Proj6
Res7 Proj5 Proj5, Proj6 Proj5, Proj6 Proj6 Proj6 Proj6
Spon1 Proj1, Proj5 Proj1, Proj2, Proj5, Proj6 Proj1, Proj2, Proj5, Proj6 Proj1, Proj2, Proj6 Proj2, Proj6 Proj2, Proj6
Spon2 Proj3 Proj3 Proj3, Proj4 Proj3, Proj4 Proj3NULL
Is that what you're after?
Interesting problem. No doubt someone will chide me for not using PIVOT!
EDIT: Forgot to mention, you'll need a SplitString function something like this one (better yet, use the one from Jeff Moden here:http://www.sqlservercentral.com/articles/Tally+Table/72993/)
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[SplitString] (@delimVARCHAR(10), @STR VARCHAR(MAX))
RETURNS @list TABLE (strcolVARCHAR(MAX))
AS
BEGIN
WITH SS(start_char, end_char) AS (
SELECT start_char = 1, end_char = CAST(CHARINDEX(@delim, @STR + @delim) AS INT)
UNION ALL
SELECT start_char = end_char + LEN(@delim), end_char = CAST(CHARINDEX(@delim, @STR + @delim, end_char + LEN(@delim)) AS INT)
FROM SS
WHERE CHARINDEX(@delim ,@str + @delim, end_char + LEN(@delim)) <> 0 )
INSERT INTO @list (strcol)
SELECT SUBSTRING(@str, start_char, end_char - start_char) AS strcol
FROM SS
RETURN
END
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 3, 2012 at 5:13 am
Dwain,
Thank you, thank you and thank you!
It will take me some time to understand your approach but the output is exactly what I needed.
Phil...
April 3, 2012 at 6:27 pm
Phil,
Actually I need to thank you as well. As it turns out, I happen to be developing a course on SQL at this moment and this particular problem illustrates at least 3-4 of the concepts being taught.
Consequently I'll probably use this example as a final exam question. Saves me the effort of coming up with one on my own!
For that, I'll need to do a solution decomposition (solved it in about 4 steps) and while it may come too late to be of use to you, I'll try to remember to post it here for others to peruse.
Dwain
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 3, 2012 at 9:02 pm
I found myself with some unexpected time on my hands this morning, so here is the solution decomposition. Note that I've slightly changed the code in a couple of places to illustrate for my class but the difference shouldn't be particularly important to you.
-- Step #1 Description: Unravel the resources column and normalize all dates to 1st of the month
SELECT Project
,DATEADD(month, DATEDIFF(month, 0, StartDate), 0) As StartDate
,DATEADD(month, DATEDIFF(month, 0, DueDate), 0) As DueDate
,x.Resources
FROM @Projects
CROSS APPLY (SELECT LTRIM(strcol) FROM SplitString(';', Resources)) AS x(Resources)
Notes:
1) Normalizing to the first of the month could be helpful to extend the solution across year boundaries.
2) Alternatively, DATEPART(Month,…) could have been used in this limited example.
-- Step #2 Description: Add the Owner and Sponsor resources
SELECT Project
,DATEADD(month, DATEDIFF(month, 0, StartDate), 0) As StartDate
,DATEADD(month, DATEDIFF(month, 0, DueDate), 0) As DueDate
,x.Resources
FROM @Projects
CROSS APPLY (SELECT LTRIM(strcol) FROM SplitString(';', Resources)) AS x(Resources)
UNION ALL
SELECT Project
,DATEADD(month, DATEDIFF(month, 0, StartDate), 0)
,DATEADD(month, DATEDIFF(month, 0, DueDate), 0), [Owner]
FROM @Projects
UNION ALL
SELECT Project
,DATEADD(month, DATEDIFF(month, 0, StartDate), 0)
,DATEADD(month, DATEDIFF(month, 0, DueDate), 0), Sponsor
FROM @Projects
Notes:
1) UNION ALL is used rather than UNION because there can be no duplicates (so no need to introduce the inefficiency of removing them).
-- Step #3 Description: Put step #2 into a CTE, introduce a tally table and use it to expand the
-- row set to span start through due dates
;WITH AllRes AS (
SELECT Project
,DATEADD(month, DATEDIFF(month, 0, StartDate), 0) As StartDate
,DATEADD(month, DATEDIFF(month, 0, DueDate), 0) As DueDate
,x.Resources
FROM @Projects
CROSS APPLY (SELECT LTRIM(strcol) FROM SplitString(';', Resources)) AS x(Resources)
UNION ALL
SELECT Project
,DATEADD(month, DATEDIFF(month, 0, StartDate), 0)
,DATEADD(month, DATEDIFF(month, 0, DueDate), 0), [Owner]
FROM @Projects
UNION ALL
SELECT Project
,DATEADD(month, DATEDIFF(month, 0, StartDate), 0)
,DATEADD(month, DATEDIFF(month, 0, DueDate), 0), Sponsor
FROM @Projects
),
Tally (n) AS (
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
)
SELECT Project, Resources, [Mof2012]
FROM AllRes
CROSS APPLY (
SELECT n FROM Tally
WHERE n BETWEEN DATEPART(month, StartDate) and DATEPART(month,DueDate)) x([Mof2012])
--SELECT 1+(DATEPART(month, StartDate)+n-2)%12 FROM Tally
--WHERE n-1 BETWEEN 0 AND DATEDIFF(month, StartDate, DueDate)) x([Month])
Notes:
1) The commented SELECT/WHERE could be used to make the return value represent the month number if the start/due dates span a year boundary.
2) Try introducing this additional project to check this:
UNION ALL SELECT 'Proj7', 'Own4', 'Spon1','2012-05-15', '2013-02-15', 'Res2'
-- Step #4 Description: Put step #3 into a CTE, and use XML on a subset of rows (subquery)
-- to create a delimited list of projects for Apr only
;WITH AllRes AS (
SELECT Project
,DATEADD(month, DATEDIFF(month, 0, StartDate), 0) As StartDate
,DATEADD(month, DATEDIFF(month, 0, DueDate), 0) As DueDate
,x.Resources
FROM @Projects
CROSS APPLY (SELECT LTRIM(strcol) FROM SplitString(';', Resources)) AS x(Resources)
UNION ALL
SELECT Project
,DATEADD(month, DATEDIFF(month, 0, StartDate), 0)
,DATEADD(month, DATEDIFF(month, 0, DueDate), 0), [Owner]
FROM @Projects
UNION ALL
SELECT Project
,DATEADD(month, DATEDIFF(month, 0, StartDate), 0)
,DATEADD(month, DATEDIFF(month, 0, DueDate), 0), Sponsor
FROM @Projects
),
Tally (n) AS (
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
),
List AS (
SELECT Project, Resources, [Mof2012]
FROM AllRes
CROSS APPLY (
SELECT n FROM Tally
WHERE n BETWEEN DATEPART(month, StartDate) and DATEPART(month,DueDate)) x([Mof2012])
)
SELECT Resources
,STUFF((SELECT ', ' + Project
FROM List l2
WHERE l2.Mof2012 = 4 and l1.Resources = l2.Resources
ORDER BY Project
FOR XML PATH('')), 1, 1, '') AS 'Apr ''12'
FROM List l1
GROUP BY Resources
ORDER BY Resources
Notes:
1) This uses the standard XML/STUFF solution for creating the delimited string column (Apr '12).
2) The final step of course is to simply copy the section starting with STUFF to create the additional columns that you need.
Hope this helps you to understand!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 4, 2012 at 7:46 am
Dwain,
It does help me understand. I also hope this procedure helps others (in this post or your course) understand.
I do need to modify the procedure to be dynamic showing a six month window starting with the current month. Is there a better way to accomplish this task other than generating a sql "loop" to create the six stuff commands with the dynamic month numbers and their alphanumeric alias's?
Thank you, again. I've gotten more from this post than I ever thought I would.
Phil...
April 4, 2012 at 6:38 pm
Phil,
I am no expert with Dynamic SQL as I try to avoid it. However I realized early on that you might want to do something like that. The issue is the column headers you want to use. The only way to generate them dynamically that I know of is with Dynamic SQL. Another alternative would be to simply label the columns something like "Current Mo," "CM+1," "CM+2," etc.
However the SQL itself can be modified to support a rolling sequence of months. Look at the SQL at the bottom of Step 3 of my solution decomp. There are a couple of statements commented out. Using those you can create a dynamic sequence of month numbers that will span Dec-Jan, but it iwll only work for a rolling 12 months. You'd need to come up with something based on year if you needed more.
To keep the Dynamic SQL as simple as possible, I'd take the results of the query and insert it into a temporary table and then construct your rolling column headers in a dynamic SQL that reads from that temporary table. You could also then only select the columns you need (1...6, 9 or 12) in the Dynamic SQL.
Hopefully that helps.
Dwain
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 5, 2012 at 2:28 am
Dwain,
Once again, thank you.
Creating a temp table and then reading from it with dynamic logic seems like my best alternative.
I will also review your "commented out" logic regarding handling the transition of calendar years.
Phil...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply