PLS Assist: Cross Tab Query - 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)

PLS Assist: Cross Tab Query

Author

Topic

amoski
Starting Member

12 Posts

Posted - 2008-05-22 : 08:39:28

Hi Guys pls assist...

im using the script below:
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

--------------------------------
And I am getting the following ERROR:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'END'.

Pls may someone assist...

Amoski

Amoski

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 03:04:58

If you instead of EXEC (@SQL) do a PRINT @SQL what is the result?
Can you spot the error then? If not, post the printed result here.

I can imagine some thing gonw wrong.

1) Table name or column name included a space
2) Columns are surrounded with single quote instead of bracket

E 12°55'05.25"
N 56°04'39.16"

amoski
Starting Member

12 Posts

Posted - 2008-05-23 : 03:16:45

quote:
Originally posted by Peso

If you instead of EXEC (@SQL) do a PRINT @SQL what is the result?
Can you spot the error then? If not, post the printed result here.

I can imagine some thing gonw wrong.

1) Table name or column name included a space
2) Columns are surrounded with single quote instead of bracket

E 12°55'05.25"
N 56°04'39.16"

This is the result after replacing Replace EXEC(@select) with PRINT(@select)

SELECT capmonth,phlo_name_x , 'GT' = count(aplcn_n END), 'NT' = count(aplcn_n END), 'NW' = count(aplcn_n END), 'RG' = count(aplcn_n END) FROM ors_hmln.a066353.APPS_NW_GT_NT_RG
GROUP BY capmonth,phlo_name_x order by capmonth,phlo_name_x asc

Amoski

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 03:22:29

Isn't it obvious where the errors are?

SELECT		capmonth,
		phlo_name_x,
		'GT' = count(aplcn_n END),
		'NT' = count(aplcn_n END),
		'NW' = count(aplcn_n END),
		'RG' = count(aplcn_n END)
FROM		ors_hmln.a066353.APPS_NW_GT_NT_RG
GROUP BY	capmonth,
		phlo_name_x
order by	capmonth,
		phlo_name_x asc

The line prodcuing this error is this

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

E 12°55'05.25"
N 56°04'39.16"

amoski
Starting Member

12 Posts

Posted - 2008-05-23 : 03:25:48

quote:

Originally posted by Peso

Isn't it obvious where the errors are?

SELECT		capmonth,
		phlo_name_x,
		'GT' = count(aplcn_n END),
		'NT' = count(aplcn_n END),
		'NW' = count(aplcn_n END),
		'RG' = count(aplcn_n END)
FROM		ors_hmln.a066353.APPS_NW_GT_NT_RG
GROUP BY	capmonth,
		phlo_name_x
order by	capmonth,
		phlo_name_x asc

The line prodcuing this error is this

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

E 12°55'05.25"
N 56°04'39.16"

Hi Peso,

I tried all possibilities to fix this line but i fail, please if you can help me in fixing it....!!

Amoski

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 03:44:05

SET @sql = ''

E 12°55'05.25"
N 56°04'39.16"

amoski
Starting Member

12 Posts

Posted - 2008-05-23 : 03:57:50

quote:
Originally posted by Peso

SET @sql = ''

E 12°55'05.25"
N 56°04'39.16"

Hi Peso,

where should i put SET @sql = ''??

Amoski

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 04:57:38

quote:
Originally posted by amoski

quote:
Originally posted by Peso

SET @sql = ''

E 12°55'05.25"
N 56°04'39.16"

Hi Peso,

where should i put SET @sql = ''??

Amoski

I think this line is causing it. Can you tell why you've included this?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 05:05:16

05/23/2008 : 03:22:29

Replace all this code
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

with this only
SET @sql=''

E 12°55'05.25"
N 56°04'39.16"

amoski
Starting Member

12 Posts

Posted - 2008-05-23 : 05:19:49

quote:
Originally posted by Peso

05/23/2008 : 03:22:29

Replace all this code
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

with this only
SET @sql=''

E 12°55'05.25"
N 56°04'39.16"

It runs fine but it doesnt give correct results:

These are the results:

200706 HOME LOANS OFFICE,BLOEMFONTEIN 2280 2280 2280 2280
200706 HOME LOANS OFFICE,CAPE TOWN 13113 13113 13113 13113
200706 HOME LOANS OFFICE,DURBAN 9141 9141 9141 9141
200706 HOME LOANS OFFICE,JOHANNESBURG 23612 23612 23612 23612
200706 HOME LOANS OFFICE,P/ELIZABETH 3794 3794 3794 3794

and i was expecting these results:

200706 HOME LOANS OFFICE,BLOEMFONTEIN 603 88 1216 373
200706 HOME LOANS OFFICE,CAPE TOWN 3668 541 7186 1718
200706 HOME LOANS OFFICE,DURBAN 2484 374 5005 1278
200706 HOME LOANS OFFICE,JOHANNESBURG 5969 853 13720 3070
200706 HOME LOANS OFFICE,P/ELIZABETH 1033 189 2068 504

if you can see it adds all numbers and put one number thru out...

Amoski

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 05:27:13

Post you calling code to the sp and we might be able to debug it.

E 12°55'05.25"
N 56°04'39.16"

amoski
Starting Member

12 Posts

Posted - 2008-05-23 : 05:33:55

quote:
Originally posted by Peso

Post you calling code to the sp and we might be able to debug it.

E 12°55'05.25"
N 56°04'39.16"

This is the code im running:

EXECUTE crosstab 'SELECT capmonth,phlo_name_x FROM ors_hmln.a066353.APPS_NW_GT_NT_RG
GROUP BY capmonth,phlo_name_x order by capmonth,phlo_name_x asc',
'count(aplcn_n)','aplcn_stat_c','ors_hmln.a066353.APPS_NW_GT_NT_RG'

the sample of my data is:

capmonth aplcn_n phlo_name_x aplcn_stat_c
200706 2482085 HOME LOANS OFFICE,CAPE TOWN RG
200707 2526490 HOME LOANS OFFICE,PRETORIA NW
200708 2582581 HOME LOANS OFFICE,DURBAN NW
200708 2576633 HOME LOANS OFFICE,CAPE TOWN NW
200706 2505348 HOME LOANS OFFICE,JOHANNESBURG NW
200706 2473731 HOME LOANS OFFICE,DURBAN RG
200706 2485644 HOME LOANS OFFICE,DURBAN GT
200706 2510016 HOME LOANS OFFICE,DURBAN NW
200707 2542767 HOME LOANS OFFICE,DURBAN NW
200709 2598192 HOME LOANS OFFICE,PRETORIA NW
200709 2624519 HOME LOANS OFFICE,JOHANNESBURG NW
200707 2544214 HOME LOANS OFFICE,JOHANNESBURG GT
200707 2512039 HOME LOANS OFFICE,DURBAN NW
200706 2490290 HOME LOANS OFFICE,CAPE TOWN NT
200707 2530797 HOME LOANS OFFICE,PRETORIA RG

Amoski

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 05:49:08

This part is never executed either!

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

The CASE ... WHEN ... is never output to statement!

E 12°55'05.25"
N 56°04'39.16"

amoski
Starting Member

12 Posts

Posted - 2008-05-23 : 05:51:37

quote:
Originally posted by Peso

This part is never executed either!

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

The CASE ... WHEN ... is never output to statement!

E 12°55'05.25"
N 56°04'39.16"

Please assist my friend...im really desparate..!!

Amoski

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 06:13:19

Use a tested and timeproven CROSSTAB like this
http://weblogs.sqlteam.com/jeffs/articles/5120.aspx

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 06:15:25

The one you used from http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables
has 195 comments. Did you read through all those to fix the error?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6216

E 12°55'05.25"
N 56°04'39.16"

amoski
Starting Member

12 Posts

Posted - 2008-05-23 : 06:33:07

quote:
Originally posted by Peso

Use a tested and timeproven CROSSTAB like this
http://weblogs.sqlteam.com/jeffs/articles/5120.aspx

E 12°55'05.25"
N 56°04'39.16"

Hi Peso,

I hope this one will work my friend can you tip on how put my excecute statment .i.e how do i call sp...!!

Amoski

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources