SQL Server - PIVOT using variable

Question

I'm trying to do a pivot in SQL Server using a column with a lot of levels. Currently I'm doing a

select '[' + category_1 + '],', count(*) from myTable group by '[' + category_1 + '],'

and copying and pasting the output into the text editor.

I was wondering if it's possible to declare a variable and use that as the IN ([],[],[]) information in the pivot command. Has anyone been able to do that?

I think it would look something like this:

@factorLevels = (select '[' + category_1 + '],' from (select category_1, count(*) from myTable) t1)

SELECT * 
FROM
(select category_1, item_count from myTable) as t1
PIVOT
(
SUM(item_count)
FOR category_1 IN (@factorLevels)
) as PivotTable;

Answer 1

If I'm understanding you correctly, I think I have a solution. You can use dynamic SQL for this. You will need to set a variable that contains all the values for the field by which you want to pivot, and then assign the query itself into a variable and execute it to get results:

DECLARE
    @values VARCHAR(50),
    @querytext VARCHAR(MAX)
;
SET @values = (SELECT '('+(SELECT STUFF(( SELECT ',[' + category_1 +']' FROM 
    <your table> FOR XML PATH('')), 1, 1, '')  )+')')
;

SELECT @querytext = 
'SELECT *
 FROM (
     SELECT
       <your fields>
     FROM
       <your table>
     WHERE
       <your criteria>
     GROUP BY
       <your grouping>
     ) AS Main
PIVOT (
    SUM(item_count)
    FOR [category_1] IN ' + @values + '
    ) AS PivotSum'

EXEC sp_executesql @querytext