EXEC crosstab -- Capture Results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

EXEC crosstab -- Capture Results

Author

Topic

bnhcomputing
Starting Member

22 Posts

Posted - 2005-09-26 : 16:04:03

I used the crosstab procedure listed below. The result set is as expected. Given that the number of columns changes dynamically, how do I capture the result of the following command:

EXEC crosstab 'select statement','summary statement', 'pivot column', 'table name'

Thanks in advance

***
CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON
GO

Hubert Hoffman

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-26 : 16:40:22

You shouldn't .... what can you do with a table that has changing column counts and names? You can't do much further processing in T-SQL ... why not keep the data normalized until the very last second you return the results to the client?

Better yet, why not return normalized data TO the client and let the client do the formatting (i.e., crosstabbing) ? What app are you using as your client/presentation layer? Can you crosstab there?

bnhcomputing
Starting Member

22 Posts

Posted - 2005-09-26 : 21:53:15

The crosstab IS the last step. I wanted to return the data to excel via DTS, but I can't get the transformation to work because of the unknown number of columns. YES, it might be possible to do the pivot in excel, but I would need a solution that didn't require any macro's, as that isn't an option to the end user. The other problem is, I had hoped to keep things simple for the user so they could easily modify the data if need be.

Give me your thoughts please, thanks.

Hubert Hoffman

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-27 : 08:44:26

quote:
Originally posted by bnhcomputing

The crosstab IS the last step. I wanted to return the data to excel via DTS, but I can't get the transformation to work because of the unknown number of columns. YES, it might be possible to do the pivot in excel, but I would need a solution that didn't require any macro's, as that isn't an option to the end user. The other problem is, I had hoped to keep things simple for the user so they could easily modify the data if need be.

Give me your thoughts please, thanks.

Hubert Hoffman

Pivot tables do not require any macros at all, and best of all, they let the user modify things on the fly -- they change the pivot columns, rows, summary formulas, etc -- in a much more flexible and dynamic and intuitive way than you can in SQL Server using a dynamic SQL cross-tab trick.

If you pre-summarize and pre-pivot the data for them, they are stuck with what you give them. If you give them the raw data in Excel and give them a 10-second demo of how pivot tables work , they won't need you to re-write your SQL 100 different ways to get the results they need. If you yourself are not sure how pivot tables work, let me know and I will point you in the right direction or do some googling. They are a powerful feature and your users should be using them.

Keep it simple, use the right tools for the right jobs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources