mickyT (11/12/2012)
michielbijnen (11/12/2012)
There is a comma! As far as I know the syntax is correct for the columns variable. Or not?As an example
select 'Count IsPrimary', [0], [1]from (select isprimary, blockid from block) b
pivot (
count(blockid)
for isprimary in ([0],[1])
) pWill work
Based on a quick glance at you procedure I'm guessing you producing something like
select 'Count IsPrimary',, [0], [1]from (select isprimary, blockid from block) b
pivot (
count(blockid)
for isprimary in (,[0],[1])
) pWhich will generate a syntax error.
If you trim the leading comma off the columns you query should start working. But that is a guess as I can't test it. You may want to change exec(@query) to select @query and post the actual query that is being generated.
nope he handles the leading "," with the initial ISNULL. the OP does not initialize the @columns variable so NULL + ',SOMETHING' is NULL and the ISNULL function returns the second value (which does not have the leading ",").
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]