Dynamic Crosstab - SQL Server Forums

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

SQL Server Development (2000)

Dynamic Crosstab

Author

Topic

chardulce
Starting Member

6 Posts

Posted - 2003-09-18 : 02:58:24

I have tried this code by (robvolk) and im very much impressed and it works perfectly when im in the SQL enterprise manager..nevertheless when i call this another stored proc which executes the 'crosstab in my ASP codes, it gaves me an error msg.

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

''''''''
this is the other stored procedure that executes the first procedure(crosstab)

CREATE PROCEDURE sp_ExecCross
AS
EXECUTE crosstab 'SELECT FinalViolation FROM DACA GROUP BY FinalViolation', 'count(FinalViolation)', 'MonthCharge', 'DACA'
GO
'''''''

Please help,,,Thank you very much.

Richard Dulce

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-09-18 : 03:10:43

What error message does it give ?

Damian

chardulce
Starting Member

6 Posts

Posted - 2003-09-18 : 20:42:20

"Incorrect syntax near END" this happens when i call the other procedure that executes the first stored proc which is the (crosstab).

thanks for the time checking my query.

richard...

chardulce
Starting Member

6 Posts

Posted - 2003-09-19 : 01:48:13

It is now running!!! but I have a problem again,,,Is it possible to place a parameter to the Stored Procedure which executes the crosstab? I mean i want to limit the data posted whenever I execute the 2nd procedure..

CREATE PROCEDURE sp_ExecCross
AS
EXECUTE crosstab 'SELECT FinalViolation FROM DACA GROUP BY FinalViolation', 'count(FinalViolation)', 'MonthCharge', 'DACA'
GO

Eg. (Original output after the 2nd stored proc)

Column ColA ColB ColC ColD ColE
One 1 1 1 1 1
Two 2 2 2 2 2
Three 3 3 3 3 3

what i want is to pass a parameter where it will only give me what i want...

eg..( I only want to post Cols from ColA to ColC)

Column ColA ColB ColC
One 1 1 1
Two 2 2 2
Three 3 3 3

help.

Wingenious
Starting Member

11 Posts

Posted - 2003-09-20 : 15:18:22

Richard,

There is a robust crosstab routine, with the ability to specify column headings in a particular order (much like Microsoft Access), included in the FREE package at this URL... [url]http://www.dbaction.com[/url]

If I understand your needs correctly, this routine will produce the results you have requested. If you have any trouble making it do so, please let me know.

Brian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-20 : 16:51:43

In the comments thread for the article:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6216

There was a modification that allowed you to specify a WHERE condition for the pivoted values, I've reprinted it here:

CREATE PROCEDURE crosstab 
@select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(100), 
@table varchar(100),
@where varchar(1000)=null
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 ' 
+ IsNull(@where,'1=1') + ' AND ' + @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

You'd execute it like so:

EXECUTE crosstab 'SELECT FinalViolation FROM DACA GROUP BY FinalViolation', 'count(FinalViolation)', 'MonthCharge', 'DACA', 'MonthCharge IN (1, 2, 3)'

Or however the WHERE condition needs to be constructed in order to return only the three values/pivot columns you're interested in.

Also read through the rest of the comments section, there are a lot of improvements posted by other SQL Team members.

chardulce
Starting Member

6 Posts

Posted - 2003-09-22 : 00:13:26

Thanks guy,

Actually, I've done a little modification on Robvolk crosstab CODE(please see below),It gave me a good result.

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')
DELETE FROM DApivot
EXEC ('INSERT INTO DApivot 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 information_schema.columns
WHERE table_name='DApivot' AND column_name='pivot'

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

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
--DROP TABLE DApivot
EXEC (@select)
SET ANSI_WARNINGS ON
GO
..
but I'm more interested on the @WHERE modification codes which rob told me..so I've tried the modification,I got this error("Incorect Syntax near ISNull") when trying to use the new strored proc..This I hope will give me the data which I want to be posted..with limitations on number of data which will I passed during the process.

richard...

Again thank you for the help,

Wingenious
Starting Member

11 Posts

Posted - 2003-09-22 : 00:49:42

Richard,

You should use whatever code you feel meets your needs the best, but...

Have you looked at the routine that I suggested? I believe it does what you are asking. It also does a lot more so you would not have to ask the forum again when your needs change.

What if you want total fields included in the query? Are you prepared to add that to the routine if/when necessary?

What if your data has no values for Col C, but you want Col C to appear anyway? I think the routine you are working with would have trouble.

What if you want to put the results into a table? You could define the result table and use INSERT/EXECUTE, but I think the routine you are working with would have trouble if the data had no values for Col A or Col B.

If you have tried the routine that I suggested and it fell short of your expectations, I would certainly like to hear about it.

Thanks,

Brian

chardulce
Starting Member

6 Posts

Posted - 2003-09-24 : 20:58:39

Rob, please help. The modification codes you've posted where @where were added gives me an error result.(Incorrect syntax near IsNull)I can't fix the code..

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

Thank you,

richard.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-24 : 21:07:00

Could your WHERE condition contain any embedded apostrophe's? If so then this should correct it:

CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100),
@where varchar(1000)=null
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 '
+ IsNull(Replace(@where,'''',''''''),'1=1') + ' AND ' + @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

Those damn sneaky apostrophe's! Man, I really need to rewrite this thing, holes everywhere.

Let me know if that fixes it. Sorry about all the goofs.

chardulce
Starting Member

6 Posts

Posted - 2003-09-25 : 04:41:20

Rob,

Thanks for the time entertaining my queries,,Im now beginning to ask myself where I AM in the SQL world!!! I cant keep your codes running, same error.By the way im using SQL 2000 and Im 3 weeks old in using SQL codes.

Again, Thank you.

richard...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources