Help Dynamic Crosstab using Recursive Select

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)

Help Dynamic Crosstab using Recursive Select

Author

Topic

JamesBraun
Starting Member

6 Posts

Posted - 2007-05-29 : 11:07:11

Hi,

I have started to create a stored procedure that returns a crosstab table. The procedure allows the user to pass through variables for fieldnames, measurements and tablenames. Everything work except for the subquery part, maybe there is something I am missing? Could anyone assist me

Here is the code

CREATE PROCEDURE [jabraun].jb_dynamic_crosstab_recursive_select_network
@tableName NVARCHAR(50),
@fieldY NVARCHAR(50),
@fieldX NVARCHAR(50),
@measure NVARCHAR(50)
AS
BEGIN
/* Procedure body */

DECLARE @XColumns NVARCHAR(1024)
SET @XColumns = ''

SELECT @XColumns
= @XColumns
+ ' SUM(Case ' + @fieldX + ' WHEN ''' + [a].[Column] + ''' THEN ' + @measure
+ ' END) AS '
+ [a].[Column] + ','
FROM
(SELECT DISTINCT @fieldX as [Column] FROM
@tableName) as a

SET @XColumns = 'SELECT ' + @fieldY + ', ' + @XColumns
+ ' SUM(' + @measure + ') as Total '
+ 'FROM ' + @tableName + ' GROUP BY ' + @fieldY + ' ORDER BY ' + @fieldY

EXEC sp_executesql @XColumns

END

The error message I get is @tableName not declared, this is in relation to the subquery part of the XColumns

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-05-29 : 13:15:00

quote:
Originally posted by JamesBraun
SELECT @XColumns
= @XColumns
+ ' SUM(Case ' + @fieldX + ' WHEN ''' + [a].[Column] + ''' THEN ' + @measure
+ ' END) AS '
+ [a].[Column] + ','
FROM
(SELECT DISTINCT @fieldX as [Column] FROM
@tableName) as a

Variables @fieldX and @tableName should be concatenated with the string and not included in the string literal.

JamesBraun
Starting Member

6 Posts

Posted - 2007-05-30 : 03:57:58

I am really stuck, would you be able to show me the syntax of this ?

Many Thanks

James

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-30 : 04:13:43

u have to include those subquery in @xcolumns...

may i know what is ur requirement..
SELECT @XColumns
= @XColumns
+ ' SUM(Case ' + @fieldX + ' WHEN ''' + [a].[Column] + ''' THEN ' + @measure
+ ' END) AS '
+ [a].[Column] + ',' +
'FROM
(SELECT DISTINCT ' + @fieldX + 'as [Column] FROM '+
@tableName+') as a'

--------------------------------------------------
S.Ahamed

JamesBraun
Starting Member

6 Posts

Posted - 2007-05-30 : 04:15:20

Thanks I will try this

JamesBraun
Starting Member

6 Posts

Posted - 2007-05-30 : 08:26:08

I have now implemented your suggestion however i get a new error

CREATE PROCEDURE [jabraun].jb_dynamic_crosstab_recursive_select_network
@tableName NVARCHAR(50),
@fieldY NVARCHAR(50),
@fieldX NVARCHAR(50),
@measure NVARCHAR(50)
AS
BEGIN

/* Procedure body */

DECLARE @XColumns NVARCHAR(1024)
SET @XColumns = ''

SELECT @XColumns
= @XColumns
+ ' SUM(Case ' + @fieldX + ' WHEN + [a].[Column] THEN '
+ @measure + ' END) AS '
+ '[a].[Column], '
+ 'FROM (SELECT DISTINCT ' + @fieldX + ' as [Column] FROM '
+ @tableName + ') as a'

SET @XColumns = 'SELECT ' + @fieldY + ', ' + @XColumns
+ ' SUM(' + @measure + ') as Total '
+ 'FROM ' + @tableName + ' GROUP BY ' + @fieldY + ' ORDER BY ' + @fieldY

EXEC sp_executesql @XColumns

END

The error is :

Line 1: Incorrect syntax near '.'.
Incorrect syntax near the keyword 'as'.

I have been staring at this for ages and cannot figure out what the problem is because the syntax appears fine

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-30 : 08:32:00

remove the comma after [a].[Column]

i donno what is ur requirement??

--------------------------------------------------
S.Ahamed

JamesBraun
Starting Member

6 Posts

Posted - 2007-05-30 : 08:46:28

The comma is needed to seperate the field names in the recursive select statement as it builds the list of fieldnames so that will not help. Even if you remove it, it gives you the same error

The requirements I have is as follows

I would like to create a dynamic crosstab using a stored procedure. The recursive select statement is much easier / faster to implement than using a cursor (that is what I read). I need to be able to pass through the name of the table that the cross tab is based on, the fieldX which is the horizontal list of vales (quantity not known, hence it needs to be dynamic), FieldY which is the vertical list of values, and finally pass through the measurement that will populate the crosstab (in my case most of the time it will be a moneyvalue).

I hope this is clearer, if anyone has any other idead / solutions I would be very grateful

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources