September 24, 2015 at 8:17 am
Trying to work out some code that involves a pivot, and I'm stuck. Grateful for some assistance...
Source table has:
[font="Courier New"]
NUMBERMileStone ForecastDateActualDate StatusFlag
1 A 1/1/2015 T
1 B 2/1/20141/1/2015 F
1 C L
2 A 3/1/2015 Q
[/font]
What I need to end up with is a table like:
[font="Courier New"]
JobNo MilestoneA_Fcst MilestoneA_Act MileStoneA_Flag MilestoneB_Fcst MilestoneB_Act MileStoneB_Flag MilestoneC_Fcst MilestoneC_Act MileStoneC_Flag
1 1/1/15 T 2/1/14 1/1/15 F T[/font]
I can get the pivot to work and produce the date columns, but it's the flag column that I can't get to work.
SELECT *
FROM (
SELECT NUMBER AS JobNo,
Milestone + 'F' AS TaskCode,
CAST(forecastdate AS SMALLDATETIME) AS TaskDate
FROM Milestonetable AS FcstDateQuery
UNION
SELECT NUMBER AS JobNo,
Milestone + 'A' AS TaskCode,
CAST(actualdate AS SMALLDATETIME) AS TaskDate
FROM Milestonetable AS ActDateQuery
) AS TaskDateQuery
PIVOT(MAX(TaskDate) FOR TaskCode IN (
[MileStoneA_Act],
[MileStoneA_Fcst],
[MileStoneB_Act],
[MileStoneB_Fcst],
[MileStoneC_Act],
[MileStoneC_Fcst]
)) AS Piv
Everything I've tried didn't come out right in the end. Figure I need some sort of nested pivot but I can't figure out how to do it.
September 24, 2015 at 8:58 am
You should try to provide a test table which will help you get help much faster.
Like this:
DECLARE @Milestonetable TABLE (JobNo INT, MileStoneName CHAR(1), ForecastDate DATE, ActualDate DATE, StatusFlag CHAR(1))
INSERT INTO @Milestonetable
VALUES (1,'A','2015/01/01',NULL,'T'),(1,'B','2014/02/01','2015/01/01','F'),(1,'C',NULL,NULL,'L'),(2,'A','2015/03/01',NULL,'Q')
SELECT * FROM @Milestonetable
Also you are selecting on columns 'NUMBER' and 'Milestone' that are not in your source table so I suspect you are missing some details.
September 24, 2015 at 9:01 am
The real tables I'm working with are monsters and have proprietary data. I'll try to build some sample tables to demonstrate.
Thanks
Edit: I realized I don't know how to provide a sample table. Are there dummy instructions somewhere on how to pull one out of my server?
September 24, 2015 at 9:26 am
Absolutely, there is a great write up here: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 24, 2015 at 9:26 am
YB already showed an example on how you should post sample data. For instructions on how to do it in a simple way, visit the following articles for 2 options:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
Here are examples on how can you do what you need using crosstabs which are easier to work with when multiple aggregations or columns are needed in a pivot. They also seem to perform better in these cases.
Here are one static and one dynamic option.
CREATE TABLE #Milestonetable(
JobNo INT,
MileStoneName CHAR(1),
ForecastDate DATE,
ActualDate DATE,
StatusFlag CHAR(1));
INSERT INTO #Milestonetable
VALUES (1,'A','2015/01/01',NULL,'T'),
(1,'B','2014/02/01','2015/01/01','F'),
(1,'C',NULL,NULL,'L'),
(2,'A','2015/03/01',NULL,'Q')
SELECT JobNo,
MAX(CASE WHEN MileStoneName = 'A' THEN ForecastDate END) AS MilestoneA_Fcst,
MAX(CASE WHEN MileStoneName = 'A' THEN ActualDate END) AS MilestoneA_Act,
MAX(CASE WHEN MileStoneName = 'A' THEN StatusFlag END) AS MileStoneA_Flag,
MAX(CASE WHEN MileStoneName = 'B' THEN ForecastDate END) AS MilestoneB_Fcst,
MAX(CASE WHEN MileStoneName = 'B' THEN ActualDate END) AS MilestoneB_Act,
MAX(CASE WHEN MileStoneName = 'B' THEN StatusFlag END) AS MileStoneB_Flag,
MAX(CASE WHEN MileStoneName = 'C' THEN ForecastDate END) AS MilestoneC_Fcst,
MAX(CASE WHEN MileStoneName = 'C' THEN ActualDate END) AS MilestoneC_Act,
MAX(CASE WHEN MileStoneName = 'C' THEN StatusFlag END) AS MileStoneC_Flag
FROM #Milestonetable
GROUP BY JobNo;
DECLARE @SQL nvarchar(max);
SET @SQL = N'SELECT JobNo ' + CHAR(10)
+ (
SELECT DISTINCT
CHAR(9) + ',MAX(CASE WHEN MileStoneName = ' + QUOTENAME( MileStoneName, '''') + ' THEN ForecastDate END) AS Milestone' + MileStoneName + '_Fcst ' + CHAR(10) +
CHAR(9) + ',MAX(CASE WHEN MileStoneName = ' + QUOTENAME( MileStoneName, '''') + ' THEN ActualDate END) AS Milestone' + MileStoneName + '_Act ' + CHAR(10) +
CHAR(9) + ',MAX(CASE WHEN MileStoneName = ' + QUOTENAME( MileStoneName, '''') + ' THEN StatusFlag END) AS MileStone' + MileStoneName + '_Flag ' + CHAR(10)
FROM #Milestonetable
--ORDER BY MileStoneName
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
+ N'FROM #Milestonetable GROUP BY JobNo;';
EXEC sp_executesql @SQL;
GO
DROP TABLE #Milestonetable;
More information in the following articles:
September 24, 2015 at 9:37 am
Luis beat me too it, I came up with the same crosstab solution that he did (his 1st query). I wanted to add that, as long as there are no duplicate JobNo and MilestoneName combinations you would want a clustered index on those two columns.
From the sample code I was creating....
CREATE TABLE #Milestonetable
(
JobNo INT NOT NULL,
MileStoneName CHAR(1),
ForecastDate DATE,
ActualDate DATE, StatusFlag CHAR(1),
CONSTRAINT PK_milestonetable PRIMARY KEY(JobNo, MileStoneName)
);
This would eliminate a sort using the Crosstabs method that Luis posted.
-- Itzik Ben-Gan 2001
September 24, 2015 at 9:57 am
Thanks guys, by the time I had written up the creation of a sample table you beat me to the how to's.
SQL Server is pretty new to me, I'm studying the solutions you have given now. I appreciate the help.
September 24, 2015 at 10:33 am
OK, works just as I needed, thank you.
I actually have hundreds of milestone names in the real table. But I'm only wanting to pull a specific set of them. With the dymanic code, is there a way to limit it to just that subset? For example, Milestones A,D,E?
September 24, 2015 at 10:36 am
If you can query the specific milestones, you can limit them.
September 24, 2015 at 10:45 am
Luis Cazares (9/24/2015)
If you can query the specific milestones, you can limit them.
Not so easy for me LOL. But I'm getting better by the hour.
I figured it out, I think. To limit to milestones A and B I added this to the dynamic code. Let me know if that's not the correct way to do it please. Seems to work correctly.
...
FROM #Milestonetable
where MileStoneName in ('A','B')
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
...
September 24, 2015 at 10:49 am
That's the way to do it. However, if you're hard coding the milestones, there's no reason to use the dynamic code. But that's the place where you would filter either by using joins or where clauses.
September 24, 2015 at 10:59 am
Luis, thank you. I'm still studying the dynamic code, to understand it. Great learning experience, I really appreciate you posting it.
It appears it will be a whole lot easier to place a list of the milestone names I want in one where statement than to hard code several lines for each milestone name in the static method. And more maintainable.
Before this query (tat is, as a feed to this query), I had built a view that took the original source table and added 2 fields based on criteria in the original, a date field and the flag field. I'm trying to see how to cobble those into this query so I can skip the intermediate view. I'm starting with the static code, then I'll move to dynamic.
Thanks again for the help.
September 24, 2015 at 12:42 pm
EDIT: Nevermind, I figured it the select into. Thanks again!
September 24, 2015 at 12:54 pm
Remember that SELECT INTO will create a new table each time. To add the INTO clause, just include it before the FROM as you would with a normal query. You might find it easier if you add a PRINT to debug your code.
DECLARE @SQL nvarchar(max);
SET @SQL =
--This is the header. It includes columns that will group the data. It could include a CTE if needed.
N'SELECT JobNo ' + CHAR(10)
--This is the dynamic part. It includes the pivoted columns
+ (
SELECT DISTINCT
CHAR(9) + ',MAX(CASE WHEN MileStoneName = ' + QUOTENAME( MileStoneName, '''') + ' THEN ForecastDate END) AS Milestone' + MileStoneName + '_Fcst ' + CHAR(10) +
CHAR(9) + ',MAX(CASE WHEN MileStoneName = ' + QUOTENAME( MileStoneName, '''') + ' THEN ActualDate END) AS Milestone' + MileStoneName + '_Act ' + CHAR(10) +
CHAR(9) + ',MAX(CASE WHEN MileStoneName = ' + QUOTENAME( MileStoneName, '''') + ' THEN StatusFlag END) AS MileStone' + MileStoneName + '_Flag ' + CHAR(10)
FROM #Milestonetable
--ORDER BY MileStoneName
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
--This is the footer. It includes everything after the column list (INTO, FROM, JOIN, APPLY, WHERE, GROUP BY, HAVING, ORDER BY) or even "totals" columns.
+ N'INTO SomeTable ' + CHAR(10)
+ N'FROM #Milestonetable ' + CHAR(10)
+ N'GROUP BY JobNo;';
PRINT @SQL;
--EXEC sp_executesql @SQL;
September 24, 2015 at 12:59 pm
Luis: Thanks. Yes a create a new table every time is what I need. I take a daily push and turn it into this new table.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply