SQL and crosstab / pivot query - this fails? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

SQL and crosstab / pivot query - this fails?

Author

Topic

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-05-15 : 11:06:53

My first stab at producing a crosstab type output from SQL.

This fails with


Msg 1087, Level 15, State 2, Line 2
Must declare the table variable "@p_l_summary".

except I have declared the table!

The code that's breaking it is


SELECT * FROM @p_l_summary AS p
PIVOT (
  SUM(PL_FIGURE) FOR PERIOD IN ( [200701], [200702], [200703], [200704], [200705], [200706], [200707], [200708], [200709], [200710], [200711], [200712], [200801], [200802], [200803], [200804], [200805], [200806], [200807], [200808], [200809], [200810], [200811], [200812], [200901], [200902], [200903], [200904], [200905], [200906], [200907], [200908], [200909], [200910], [200911], [200912], [201001], [201002], [201003], [201004], [201005], [201006], [201007], [201008], [201009], [201010], [201011], [201012], [201101], [201102], [201103], [201104], [201105], [201106], [201107], [201108], [201109], [201110], [201111], [201112], [201201], [201202], [201203], [201204], [201205], [201206], [201207], [201208], [201209], [201210], [201211], [201212], [201301], [201302], [201303], [201304], [201305], [201306], [201312])
) AS PL_BY_MONTH

Oddly, if I add


SELECT * FROM @p_l_summary
PIVOT (SUM(PL_FIGURE)
FOR PERIOD IN ([201201], [201202]))
AS PL_BY_MONTH

at the end of the script, it runs!

Any ideas?

Thanks in advance.

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-05-15 : 11:08:59

Forgot to add, the code that's breaking it is derived from a PRINT @sql command where
@sql = SET @columns = N''
SELECT @columns += N', ' + QUOTENAME(PERIOD)
FROM (SELECT DISTINCT PERIOD FROM @p_l_summary) AS p
SET @sql_two = N'
SELECT * FROM @p_l_summary AS p
PIVOT (
SUM(PL_FIGURE) FOR PERIOD IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) AS PL_BY_MONTH';

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-05-15 : 15:25:40

Anyone?

I guessed it might be a step too far.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-15 : 18:22:52

The code in the second post does not parse - the first line does not seem like valid SQL syntax. --> @sql = set columns = N''

The error message in the original post tells me that you don't have the variable @p_l_summary defined in scope. Can you post the entire query string generated? If it does not include a "declare @p_l_summary table...." it should.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 00:14:52

quote:
Originally posted by Rasta Pickles

Anyone?

I guessed it might be a step too far.

whats purpose of this?

@sql = SET @columns = N''

Its not even syntactically correct.
Also is @p_l_summary a table variable or is it a string variable containing table name as its value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-05-16 : 15:08:53

Thank you for replying but I've solved it.

For info, I was running an EXEC @sql command on the


@sql = SET @columns = N''
SELECT @columns += N', ' + QUOTENAME(PERIOD)
FROM (SELECT DISTINCT PERIOD FROM @p_l_summary) AS p
SET @sql_two = N'
SELECT * FROM @p_l_summary AS p
PIVOT (
SUM(PL_FIGURE) FOR PERIOD IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) AS PL_BY_MONTH';

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 00:04:18

even then there should be ' enclosing SET ... statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources