PIVOT function usage - Help needed
-
Hello,
I have a table with the following columns...
CustomerID, OrderID, OrderDate, OrderAmount
The data looks as attached image "OriginalData.jpeg".
I am trying to dynamically pivot this data to get the OrderAmount for yyyy/mm. Since the column list for mm/yyyy is dynamic, I am building dynamic sql to get this list built and pivot all corresponding data. I am getting most of it right.
Following is a sample script that creates the tables, data and dynamic SQL for pivoting...
-- Begin of sample script
IF OBJECT_ID('tempdb..#TestTable1') IS NOT NULL
BEGIN
DROP TABLE #TestTable1
END
-- Create data and fill out sample data
CREATE TABLE #TestTable1 (CustomerID INT, OrderID INT, OrderDate DateTime, OrderAmount MONEY)
INSERT #TestTable1 VALUES (1, 101, '7/4/2011', 12856.76)
INSERT #TestTable1 VALUES (1, 101, '8/11/2011', 46565.33)
INSERT #TestTable1 VALUES (1, 103, '9/23/2011', 76874.29)
INSERT #TestTable1 VALUES (2, 229, '8/13/2011', 38876.31)
INSERT #TestTable1 VALUES (2, 248, '10/21/2011', 87644.81)
INSERT #TestTable1 VALUES (3, 391, '7/4/2011', 9866.47)
INSERT #TestTable1 VALUES (3, 391, '10/1/2011', 48872.91)
INSERT #TestTable1 VALUES (3, 391, '11/15/2011', 28331.07)
INSERT #TestTable1 VALUES (4, 432, '11/17/2011', 28713.88)
INSERT #TestTable1 VALUES (4, 455, '11/29/2011', 56721.03)
INSERT #TestTable1 VALUES (4, 455, '12/02/2011', 38004.79)
IF OBJECT_ID('tempdb..#TestTable2') IS NOT NULL
BEGIN
DROP TABLE #TestTable2
END
-- Create an intermediate table to hold yyyy/mm format for each order.
-- This is done to make it easier to handle for dynamic sql in pivot.
-- Possibly, there is a better way out directly to pivot from table1.
-- But I resorted to this trick to arrive at a quick solution
CREATE TABLE #TestTable2 (CustomerID INT, OrderID INT, OrderYearMonth VARCHAR (7), OrderAmount MONEY)
INSERT #TestTable2
SELECT CustomerID, OrderID,
CAST (YEAR (OrderDate) AS VARCHAR)
+ '/'
+
CASE LEN (CAST (MONTH (OrderDate) AS VARCHAR))
WHEN 1 THEN '0'
ELSE ''
END
+ CAST (MONTH (OrderDate) AS VARCHAR)
OrderYearMonth
, OrderAmount
FROM #TestTable1
-- Build the mm/yyyy list
DECLARE @YearMonthList NVarchar (MAX)
SELECT @YearMonthList =
STUFF (
(
SELECT DISTINCT ',[' + OrderYearMonth + ']'
FROM #TestTable2
FOR XML PATH ('')
),
1, 1, '')
-- Pivot data dynamically
DECLARE @SQL NVarchar (MAX)
SELECT @SQL = N'
SELECT TOP 100 PERCENT * FROM (
SELECT CustomerID, OrderID, OrderYearMonth,
SUM (OrderAmount) MonthlyOrderAmount
FROM #TestTable2
GROUP BY CustomerID, OrderID, OrderYearMonth
) Data
PIVOT (SUM (MonthlyOrderAmount) FOR
OrderYearMonth IN (
' + @YearMonthList + ')
) PivotTable
'
EXEC sp_ExecuteSQL @SQL
-- End of sample script
I am getting everything as desired so far (see attached image PivotedOutput.jpeg).
However I am looking to get an additional column which is a sum of all mm/yyyy columns for each row. A sample desired output can be found in image DesiredPivotedOutput.jpeg
Once I get the desired output from the PIVOT, I need to be able to put this in a temp table or a table variable. The rest of my stored procedure has a bigger join that would use this table as a part of it. Since the column list (for yyyy/mm) varies based on the data, how do I create this temp table and insert results from my dynamic PIVOT function?
I would appreciate if you could help me find a solution for this.
- Helios
-
Not a hard thing to do, but let me ask u one question ; how many distinct months are u expecting to be present in ur source table?
-
It all depends on the date range a given user selects for the report. Since data is avaialble for about 10 years, in theory it could vary from 1 to 120. But practically, I would say about 24-36 months.
- Helios
-
A grand total column is one of the very reasons why I don't use PIVOT. Please see the following article for an alternative and comparison against the use of PIVOT. As a side benefit, the alternative is usually faster than PIVOT.
http://qa.sqlservercentral.com/articles/T-SQL/63681/
The second article in that 2 part series shows how to do it in dynamic SQL...
http://qa.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply