michielbijnen (11/12/2012)

---

There is a comma! As far as I know the syntax is correct for the columns variable. Or not?

select 'Count IsPrimary', [0], [1]
from (select isprimary, blockid from block) b
	pivot (
	count(blockid)
	for isprimary in ([0],[1])
	) p

select 'Count IsPrimary',, [0], [1]
from (select isprimary, blockid from block) b
	pivot (
	count(blockid)
	for isprimary in (,[0],[1])
	) p

michielbijnen (11/12/2012)

---

There is a comma! As far as I know the syntax is correct for the columns variable. Or not?

mickyT (11/12/2012)

---

As an example

select 'Count IsPrimary', [0], [1]
from (select isprimary, blockid from block) b
	pivot (
	count(blockid)
	for isprimary in ([0],[1])
	) p

Will 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])
	) p

Which 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.

capnhector (11/12/2012)

---

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 ",").