I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
Check out the PIVOT operator
|||
Mike, check out this sample script. You would need a case statement for each month, this is just using one for each month supplied in the example.
Code Snippet
CREATE TABLE #currentTable (
Project NVARCHAR(10),
Task NVARcHAR(10),
Category NVARcHAR(10),
FiscalYear INT,
FiscalMonth INT,
TotalHours INT
)
INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 1', 2007, 01, 40)
INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 2', 2007, 02, 20)
INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 3', 2007, 03, 35)
INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 1', 2008, 01, 40)
INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 2', 2008, 02, 40)
INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 3', 2008, 03, 40)
SELECT Project, Task, Category, FiscalYear,
SUM(CASE FiscalMonth WHEN 1 THEN TotalHours ELSE 0 END) AS '01',
SUM(CASE FiscalMonth WHEN 2 THEN TotalHours ELSE 0 END) AS '02',
SUM(CASE FiscalMonth WHEN 3 THEN TotalHours ELSE 0 END) AS '03'
FROM #currentTable
GROUP BY Project, FiscalYear, Task, Caetegory
DROP TABLE #currentTable
|||
ShawnNSF,
When I run this statement the results in query analyzer it pulls back the results that I want to see on the bottom of the results pane based off of the the Selection statement looking into X_PJLABDIS(view).
My problem with this script is the INSERT step. It adds the values in the parantheses and I need it to add the values returned from the SELECT Statement below that.
Mike
CREATE TABLE PIVOT (
Project NVARCHAR(10),
pjt_entity NVARcHAR(10),
acct NVARcHAR(10),
Fiscalno_year INT,
Fiscalno_month INT,
total_hrs INT
)
INSERT INTO PIVOT VALUES ('Project', 'Pjt_entity', 'acct', 2007, 01, 40)
SELECT Project, pjt_entity, acct, Fiscalno_year,
SUM(CASE Fiscalno_month WHEN 1 THEN total_hrs ELSE 0 END) AS 'Month 01',
SUM(CASE Fiscalno_month WHEN 2 THEN total_hrs ELSE 0 END) AS 'Month 02',
SUM(CASE Fiscalno_month WHEN 3 THEN total_hrs ELSE 0 END) AS 'Month 03',
SUM(CASE Fiscalno_month WHEN 4 THEN total_hrs ELSE 0 END) AS 'Month 04',
SUM(CASE Fiscalno_month WHEN 5 THEN total_hrs ELSE 0 END) AS 'Month 05',
SUM(CASE Fiscalno_month WHEN 6 THEN total_hrs ELSE 0 END) AS 'Month 06',
SUM(CASE Fiscalno_month WHEN 7 THEN total_hrs ELSE 0 END) AS 'Month 07',
SUM(CASE Fiscalno_month WHEN 8 THEN total_hrs ELSE 0 END) AS 'Month 08',
SUM(CASE Fiscalno_month WHEN 9 THEN total_hrs ELSE 0 END) AS 'Month 09',
SUM(CASE Fiscalno_month WHEN 10 THEN total_hrs ELSE 0 END) AS 'Month 10',
SUM(CASE Fiscalno_month WHEN 11 THEN total_hrs ELSE 0 END) AS 'Month 11',
SUM(CASE Fiscalno_month WHEN 12 THEN total_hrs ELSE 0 END) AS 'Month 12'
FROM X_PJLABDIS
GROUP BY Project, Fiscalno_year, pjt_entity, acct
|||
Mike,
Those values are just for example. Do not use that part of the script. You seem to have gotten the idea by modifying the select statement (the part that was for you). I should have explained a little better that the other part of my snippet was just me building test data to show you the example query.
You can just run a query with the same idea as what I posted against the table you have with the data already populated in it.
|||
Shawn,
As you can probably tell right now, I am have very limited knowledge of and using pivot tables and stored procedures. As you said I am going down the right path with this code.
How would I go about achieving returning the results into a report or table? Need all the help I can get right now and you are helping a lot and very much appreciate it.
Thanks,
Mike
|||
FYI, using pivot with SS 2005
Code Snippet
Create Table #Project (
Project nvarchar(10),
Task nvarchar(10),
Category nvarchar(10),
FisicalYear int,
FisicalMonth int,
totalhrs Int
);
--Sample Data
Insert Into #Project Values('Project 1','Task 1', 'Cat 1', '2007', '1', '40' );
Insert Into #Project Values('Project 1','Task 1', 'Cat 2', '2007', '2', '20' );
Insert Into #Project Values('Project 1','Task 1', 'Cat 3', '2007', '3', '35' );
Insert Into #Project Values('Project 1','Task 1', 'Cat 4', '2007', '4', '40' );
Insert Into #Project Values('Project 1','Task 1', 'Cat 5', '2007', '5', '20' );
Insert Into #Project Values('Project 1','Task 1', 'Cat 6', '2007', '6', '35' );
Insert Into #Project Values('Project 1','Task 1', 'Cat 7', '2007', '7', '40' );
Insert Into #Project Values('Project 1','Task 1', 'Cat 8', '2007', '8', '20' );
Insert Into #Project Values('Project 1','Task 1', 'Cat 9', '2007', '9', '35' );
Insert Into #Project Values('Project 1','Task 1', 'Cat 10', '2007', '10', '40' );
Insert Into #Project Values('Project 1','Task 1', 'Cat 11', '2007', '11', '20' );
Insert Into #Project Values('Project 1','Task 1', 'Cat 12', '2007', '12', '35' );
Insert Into #Project Values('Project 1','Task 1', 'Cat 1', '2008', '01', '40' );
Insert Into #Project Values('Project 1','Task 1', 'Cat 2', '2008', '02', '40' );
Insert Into #Project Values('Project 1','Task 1', 'Cat 3', '2008', '03', '40' );
--Pivot Query
SELECT Project,Task, Category, FisicalYear,
ISNULL([1],0) AS 'Month 01',ISNULL([2],0) AS 'Month 02',ISNULL([3],0) AS 'Month 03',ISNULL([4],0) AS 'Month 04',
ISNULL([5],0) AS 'Month 05',ISNULL([6],0) AS 'Month 06',ISNULL([7],0) AS 'Month 07',ISNULL([8],0) AS 'Month 08',
ISNULL([9],0) AS 'Month 09',ISNULL([10],0) AS 'Month 10',ISNULL([11],0) AS 'Month 11',ISNULL([12],0) AS 'Month 12'
FROM (SELECT Project, Task, Category, FisicalYear,FisicalMonth,totalhrs,
Row_NUmber() OVER (partition by FisicalYear Order by FisicalMonth) as RowNum
FROM #Project) p
PIVOT
(SUM(totalhrs) FOR FisicalMonth IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))
AS pvt
ORDER BY FisicalYear, RowNum
--
--SELECT Project,Task, Category, FisicalYear,
--SUM(CASE FisicalMonth WHEN 1 THEN totalhrs ELSE 0 END) AS 'Month 01',
--SUM(CASE FisicalMonth WHEN 2 THEN totalhrs ELSE 0 END) AS 'Month 02',
--SUM(CASE FisicalMonth WHEN 3 THEN totalhrs ELSE 0 END) AS 'Month 03',
--SUM(CASE FisicalMonth WHEN 4 THEN totalhrs ELSE 0 END) AS 'Month 04',
--SUM(CASE FisicalMonth WHEN 5 THEN totalhrs ELSE 0 END) AS 'Month 05',
--SUM(CASE FisicalMonth WHEN 6 THEN totalhrs ELSE 0 END) AS 'Month 06',
--SUM(CASE FisicalMonth WHEN 7 THEN totalhrs ELSE 0 END) AS 'Month 07',
--SUM(CASE FisicalMonth WHEN 8 THEN totalhrs ELSE 0 END) AS 'Month 08',
--SUM(CASE FisicalMonth WHEN 9 THEN totalhrs ELSE 0 END) AS 'Month 09',
--SUM(CASE FisicalMonth WHEN 10 THEN totalhrs ELSE 0 END) AS 'Month 10',
--SUM(CASE FisicalMonth WHEN 11 THEN totalhrs ELSE 0 END) AS 'Month 11',
--SUM(CASE FisicalMonth WHEN 12 THEN totalhrs ELSE 0 END) AS 'Month 12'
--
--FROM #Project
--
--GROUP BY Project,Task, Category, FisicalYear
--cleanup
drop table #Project
