PIVOT SQL data and space filling

Question

I am trying to convert this data:

ItemID MonthAsInt Month Year InvType Quantity 4643 4 April 2011 Shipment 10 4643 5 May 2011 Shipment 10 4643 7 July 2011 Shipment 10 4643 8 August 2011 Destroy 10 4643 11 November 2011 Shipment 25 4643 12 December 2011 Picking 1 

To this (basically a 12 second click):

  February March April May June July August ... Shipment 0 0 10 10 0 10 0 Picking 0 0 0 0 0 0 0 Destroy ... 

I messed up the PIVIOT method but no luck. At this point, all I have is a list of dates that I need between GETDATE() and GETDATE() - 12 months (retrieved using the query below):

 DECLARE @BeginDate DATETIME DECLARE @EndDate DATETIME SET @BeginDate = GETDATE(); SET @EndDate = DATEADD(MONTH, -12, GETDATE()); WITH CTE_DatesTable AS ( SELECT @EndDate AS [Date] UNION ALL SELECT DATEADD(dd, 1, [date]) FROM CTE_DatesTable WHERE DATEADD(dd, 1, [date]) <= @BeginDate ) SELECT DISTINCT DATENAME(MONTH, [date]) + ' ' + CAST(YEAR([date]) AS VARCHAR(4)) AS MonthYear, YEAR([date]) AS YearAsInt, MONTH([Date]) AS MonthAsInt FROM CTE_DatesTable ORDER BY YEAR([date]), MONTH([Date]) OPTION (MAXRECURSION 0) 

See request in action here .

Am I trying to do what I'm trying to do? Am I going in the right direction? Any help would be appreciated!

Answer 1

You can do this without pivot (the syntax for which I find also complicated). Since you do not know the actual arrangement of the columns in advance, I think this is easiest with dynamic SQL. Given the following table / sample data:

 USE tempdb; GO CREATE TABLE dbo.foo ( ItemID INT, MonthAsInt INT, [Month] VARCHAR(12), [Year] INT, InvType VARCHAR(12), Quantity INT ); INSERT dbo.foo SELECT 4643,4 ,'April ',2011,'Shipment',10 UNION ALL SELECT 4643,5 ,'May ',2011,'Shipment',10 UNION ALL SELECT 4643,7 ,'July ',2011,'Shipment',10 UNION ALL SELECT 4643,8 ,'August ',2011,'Destroy ',10 UNION ALL SELECT 4643,11,'November',2011,'Shipment',25 UNION ALL SELECT 4643,12,'December',2011,'Picking ',1; 

You can create a list of months using the much simpler CTE and build on the basis of this dynamic SQL expression:

 DECLARE @sql NVARCHAR(MAX) = N''; ;WITH n AS ( SELECT TOP (12) d = DATEADD ( MONTH, -(ROW_NUMBER() OVER (ORDER BY [object_id]) - 1), GETDATE() ) FROM sys.objects ORDER BY d DESC ) SELECT @sql = @sql + N',' + CHAR(13) + CHAR(10) + DATENAME(MONTH, d) + ' = SUM(CASE WHEN [Year] = ' + CONVERT(VARCHAR(4), DATEPART(YEAR, d)) + ' AND MonthAsInt = ' + CONVERT(VARCHAR(2), DATEPART(MONTH, d)) + ' THEN Quantity ELSE 0 END)' FROM n ORDER BY d; SELECT @sql = N'SELECT InvType' + @sql + ' FROM dbo.foo GROUP BY InvType'; PRINT @sql; -- EXEC sp_executesql @sql; 

I put the PRINT there so you can check it out before running it. I was not sure if you need 12 months or 13 months, you can simply change TOP (12) to TOP (13) if you want 13 months, or delete -1 if you do not want the current month to be included.

Answer 2

If you need a dynamic axis: see here

otherwise

Here's how you do it. Of course, PIVOT requires you to know what your data will look like in advance. If you need a dynamic point, then there are many dynamic cross tabs / pivot requests / sps that are already written.

 DECLARE @BeginDate DATETIME DECLARE @EndDate DATETIME SET @BeginDate = GETDATE(); SET @EndDate = DATEADD(MONTH, -12, GETDATE()); WITH CTE_DatesTable AS ( SELECT @EndDate AS [Date] UNION ALL SELECT DATEADD(dd, 1, [date]) FROM CTE_DatesTable WHERE DATEADD(dd, 1, [date]) <= @BeginDate ) SELECT DISTINCT DATENAME(MONTH, [date]) + ' ' + CAST(YEAR([date]) AS VARCHAR(4)) AS MonthYear, YEAR([date]) AS YearAsInt, MONTH([Date]) AS MonthAsInt, case when month([date]) < 5 then 'Shipment' else 'Picking' end InvType, floor(10 * RAND() * month([date])) Quantity into #orig FROM CTE_DatesTable ORDER BY YEAR([date]), MONTH([Date]) OPTION (MAXRECURSION 0) update #orig set Quantity = null where MonthYear = 'February 2011' select * from #orig select * from ( select isnull(Quantity, 0) Quantity, MonthYear from #orig ) SourceTbl PIVOT ( sum(Quantity) for MonthYear in ([February 2011], [March 2011]) ) PivotTbl drop table #orig 

Result:

 February 2011 March 2011 0 29