quote:

---

Originally posted by amoski

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

---

quote:

---

Originally posted by amoski

[quote]Originally posted by visakh16

Replace EXEC(@select) with PRINT(@select) and post the string its printing out.

Hi,

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

It puts END in every count...



Amoski

---

quote:

---

Originally posted by amoski



I believe problem is with no proper ending to CASE statement I highlighted above.

Harsh Athalye

Hi Athalye,

Pls help with correcting the Case statement i tried all posibilities my friend and i failed...!!

Amoski

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by amoski



I believe problem is with no proper ending to CASE statement I highlighted above.

Harsh Athalye

Hi Athalye,

Pls help with correcting the Case statement i tried all posibilities my friend and i failed...!!

Amoski

---

can you post how you will be calling the sp?

---