July 2, 2014 at 12:18 pm
Hello there,
I am having trouble figuring out why the following code throws an error:
declare
@cols nvarchar(50),
@stmt nvarchar(max)
select @cols = ('[' + W.FKStoreID + ']') from (select distinct FKStoreID from VW_PC_T) as W
select @stmt = '
select *
from VW_PC_T as T
pivot
(
SUM(SalesPrice)
for T.FKStoreID in (' + @cols + ')
) as P'
exec sp_executesql @stmt = @stmt
the issue that I am having is:
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '[' to data type int.
I know that I have to use the [ ] in order to run the dynamic sql. I am not sure what is failing and why as the syntax seems to be clean to me (obviously it is not). I need a second (more experienced) set of eyes to guide me in this troubleshooting.
thanks in advance for help or pointing me in the right direction.
PEtr
July 2, 2014 at 12:26 pm
vecerda (7/2/2014)
Hello there,I am having trouble figuring out why the following code throws an error:
declare
@cols nvarchar(50),
@stmt nvarchar(max)
select @cols = ('[' + W.FKStoreID + ']') from (select distinct FKStoreID from VW_PC_T) as W
select @stmt = '
select *
from VW_PC_T as T
pivot
(
SUM(SalesPrice)
for T.FKStoreID in (' + @cols + ')
) as P'
exec sp_executesql @stmt = @stmt
the issue that I am having is:
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '[' to data type int.
I know that I have to use the [ ] in order to run the dynamic sql. I am not sure what is failing and why as the syntax seems to be clean to me (obviously it is not). I need a second (more experienced) set of eyes to guide me in this troubleshooting.
thanks in advance for help or pointing me in the right direction.
PEtr
Not quite sure what you are trying to do here but I don't think this is quite right.
This is not at all right...
select @cols = ('[' + W.FKStoreID + ']') from (select distinct FKStoreID from VW_PC_T) as W
What are the square brackets for? Do you realize that @cols is only going to have a single value and not all the values from your subquery?
You should change your exec to a select so you can see the contents of your dynamic sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2014 at 12:29 pm
OK< thanks, what I am trying to do is use pivot to create cross-tab between items and stores. Basically I need to figure out what each item costs in each store.
Maybe I am going about it the wrong way altogether. It looked like a good case for a dynamic pivot.
thanks,
Petr
July 2, 2014 at 12:33 pm
vecerda (7/2/2014)
OK< thanks, what I am trying to do is use pivot to create cross-tab between items and stores. Basically I need to figure out what each item costs in each store.Maybe I am going about it the wrong way altogether. It looked like a good case for a dynamic pivot.
thanks,
Petr
Maybe a dynamic pivot would work but what you have isn't going to work. You have a list of ints wrapped in square brackets.
In essence your dynamic code is something like this.
select *
from VW_PC_T as T
pivot
(
SUM(SalesPrice)
for T.FKStoreID in ([12]) --only 1 value here because of faulty logic when setting value for @cols
) as P
Obviously that code isn't going to work.
I would think that for something like this a dynamic cross tab would be the way to go. Take a look at the links in my signature for cross tabs.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2014 at 12:35 pm
You sir! are a a lifesaver. I see how this is a much better way of doing this,
thanks for pointing me in right direction,
Petr
July 2, 2014 at 12:38 pm
vecerda (7/2/2014)
You sir! are a a lifesaver. I see how this is a much better way of doing this,thanks for pointing me in right direction,
Petr
You are quite welcome. The crosstab is easier to code and understand in my opinion. It also has the distinct advantage of almost always being faster. It is a triple win!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2014 at 12:39 pm
Yes sir, thanks again,
Petr
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply