April 5, 2006 at 9:24 am
I have a table with the following structure:
CREATE TABLE [#TEST] (
[sCalldate] [varchar]((20) NULL ,
[sProject] [varchar] (20) NULL ,
[dHours] numeric (10,4) NULL)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'A', 12)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'B', 0)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'C', 2)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'A', 2)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'B', 5)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'C', 4)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'A', 3)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'B', 5)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'C', 1)
I need to create an excel report that contains the calldate down the first column and the projects as cloumn headings across the top with the amount of hours worked each projects per day.
I created a cross tab table with project codes across the top as columns. One major issue here is that project codes are created daily. One day I can have 3 next day I can have 5.
So I have the table and the data populated the way I need, but I can't create the excel spreadsheet in a dts package (this report needs to be run on a nightly basis).
Can anyone help me? Is there any way of accomplishing this?
Thanks,
Ninel
April 5, 2006 at 8:08 pm
No idea if this will work in DTS, but here is an option.
I wasn't born stupid - I had to study.
April 6, 2006 at 5:42 am
You may have better luck exporting the raw data to Excel, and then using a pivot table in Excel.
Or you could look into OLAP cubes.
Dynamic cross-tab queries are, in my experience, tricky in T-SQL.
April 6, 2006 at 7:45 am
Here is a crosstab SP that was posted a while back. I think this is what you are looking for.
EXEC sp_CrossTab
@table = 'TEST',
@onrows = 'sCalldate',
@onrowsalias = 'dhours',
@oncols = 'sproject',
@sumcol = 'dhours'
USE master
CREATE PROC sp_CrossTab
@table AS sysname, -- Table to crosstab
@onrows AS nvarchar(128), -- Grouping key values (on rows)
@onrowsalias AS sysname = NULL, -- Alias for grouping column
@oncols AS nvarchar(128), -- Destination columns (on columns)
@sumcol AS sysname = NULL -- Data cells
AS
--LISTING 4: Step 1 of the sp_CrossTab Stored Procedure: Beginning of the SQL String
DECLARE
@sql AS varchar(8000),
@NEWLINE AS char(1)
SET @NEWLINE = CHAR(10)
-- step 1: beginning of SQL string
SET @sql =
'SELECT' + @NEWLINE +
' ' + @onrows +
CASE
WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
ELSE ''
END
--LISTING 5: Step 2 of the sp_CrossTab Stored Procedure: Storing Keys in a Temp Table
CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)
DECLARE @keyssql AS varchar(1000)
SET @keyssql =
'INSERT INTO #keys ' +
'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
'FROM ' + @table
EXEC (@keyssql)
--LISTING 6: Step 3 of the sp_CrossTab Stored procedure: Middle Part of SQL String
DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys
WHILE @key IS NOT NULL
BEGIN
SET @sql = @sql + ',' + @NEWLINE +
' SUM(CASE CAST(' + @oncols +
' AS nvarchar(100))' + @NEWLINE +
' WHEN N''' + @key +
''' THEN ' + CASE
WHEN @sumcol IS NULL THEN '1'
ELSE @sumcol
END + @NEWLINE +
' ELSE 0' + @NEWLINE +
' END) AS c' + @key
SELECT @key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @key
END
--LISTING 7: Step 4 of the sp_CrossTab Stored Procedure: End of SQL String
SET @sql = @sql + @NEWLINE +
'FROM ' + @table + @NEWLINE +
'GROUP BY ' + @onrows + @NEWLINE +
'ORDER BY ' + @onrows
-- PRINT @sql + @NEWLINE -- For debug
EXEC (@sql)
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply