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.
| Author |
Topic |
|
vmon
Yak Posting Veteran
63 Posts |
 Posted - 2003-05-15 : 18:16:45
|
| Hi,I am using Rob Volks proc to crosstab a table and I have added the WHERE clause to the end but can't seem to make it work. I get an error near the WHERE message. Here is my execute statment.EXECUTE crosstab 'SELECT Caseworker FROM tblWeeklyAgingByCaseworker GROUP BY Caseworker', ' SUM(Amount)', 'CONVERT(varchar(8),BucketDate,1)', 'tblWeeklyAgingByCaseworker', 'WHERE DATE_CALC = @txtPeriod)'I suspect it might be because I am using an @variable. Any suggestions?Thanks vmon |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-16 : 18:51:04
|
| Yes, something like this should work:EXECUTE crosstab 'SELECT Caseworker FROM tblWeeklyAgingByCaseworker GROUP BY Caseworker', ' SUM(Amount)', 'CONVERT(varchar(8),BucketDate,1)', 'tblWeeklyAgingByCaseworker', 'WHERE DATE_CALC =' + cast(@txtPeriod as varchar)If @txtPeriod is a date or string value, then use this:EXECUTE crosstab 'SELECT Caseworker FROM tblWeeklyAgingByCaseworker GROUP BY Caseworker', ' SUM(Amount)', 'CONVERT(varchar(8),BucketDate,1)', 'tblWeeklyAgingByCaseworker', 'WHERE DATE_CALC =''' + cast(@txtPeriod as varchar) + ''''The reason it didn't work earlier was indeed because of the variable. The cross tab procedure is all dynamic SQL, so it cannot see a variable that is declared outside of the SQL string it generates. |
 |
|
|
vmon
Yak Posting Veteran
63 Posts |
Posted - 2003-05-19 : 09:10:12
|
| Thank you! I will give that a try when I get to work. One other question. Will the dynamic crosstab procedure work with a temporary table? I want to insert records into a temporary table. I saw a post sometime ago about it not working but someone was going to change it to accomodate ##tablename.Thanks again,vmon |
|
|
|
|
|
|
|
|
Dynamic crosstab with WHERE