sql server dts: How to get column headings on a job in DTS -- sql server dts

Re: How to get column headings on a job in DTS

Blasting Cap
1/15/2007 3:18:21 PM

To answer your questions -

Outputting directly to Excel worksheet (a function with DTS).

The table that I am doing the crosstab on is one that I'm building,
based generally on a Business Area Code (10, 11, 12, 15, 20, etc) and
the Channel in which it's sold under. Channel could vary from
month-to-month, as could the Business Area Code.

It's like:

BA ShipQty DC
10 50 DL
11 25 DL
12 50 CH
14 100 WF

Report would look like:

BA DL CH WF
10 50 0 0
11 25 0 0
12 0 50 0
14 0 0 100

I may have no DL shipments next month, but could have shipments in CH in
many business areas next month.

The SP I am using is as follows:

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

And gives me the output I am looking for. I just need the column
headings for that month as well, since they are subject to change from
month to month. I may have DL absent from the report next month, and
may have 3 new ones not on the report this month. The columns would
always be shifting back & forth, and I either need a way to keep them
static & put the appropriate info in the appropriate place (and do this
without hardcoding them), or to have them dynamically show up & vanish
from month to month (while retaining the "key" to the columns).

The SP above is called by:

sp_crosstab 'sales_worktable',level_1_1,'BA',distr_channel,ship_val

where level_1_1 corresponds to the BA above, 'BA' is what the column is
called in the report, distr_channel is the column header, and ship_val
is what I am totalling up.

BC

[quoted text, click to view]

Re: How to get column headings on a job in DTS

JXStern
1/15/2007 3:36:36 PM

On Mon, 15 Jan 2007 15:18:21 -0500, Blasting Cap
[quoted text, click to view]

OK. Haven't used it myself.

[quoted text, click to view]

....

OK, that's pretty fancy code, can't you just parse that and create a
first output line explicitly? No elegance there, but it works.

I've been recently doing sort of the opposite, calculating a "control"
record that's supposed to be the first record in a sequential CSV or
fixed format output file, lists the record count, sums critical
fields. I just UNION ALL it into the output stream - especially since
I can't find a way in DTS to output it separately and then APPEND to
it!

J.

Re: How to get column headings on a job in DTS

Blasting Cap
1/15/2007 3:36:56 PM

BTW, I am using SQL 2000 on the server this application has to run on.
Using Windows 2003 as well.....

BC

[quoted text, click to view]

Re: How to get column headings on a job in DTS

Blasting Cap
1/16/2007 2:30:37 PM

Unfortunately, that's kinda where I am at too - sort of a "you can't get
there from here" type of thing.

The only thing that does though, is create a maintenance nightmare to
deal with.

BC

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.