SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL and crosstab / pivot query - this fails?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 05/15/2013 :  11:06:53  Show Profile  Reply with Quote
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

United Kingdom
171 Posts

Posted - 05/15/2013 :  11:08:59  Show Profile  Reply with Quote
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';
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 05/15/2013 :  15:25:40  Show Profile  Reply with Quote
Anyone?

I guessed it might be a step too far.

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3638 Posts

Posted - 05/15/2013 :  18:22:52  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/16/2013 :  00:14:52  Show Profile  Reply with Quote
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
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 05/16/2013 :  15:08:53  Show Profile  Reply with Quote
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';
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/17/2013 :  00:04:18  Show Profile  Reply with Quote
even then there should be ' enclosing SET ... statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000