SQL Server dynamic pivot unknown number of columns
- DECLARE @v_query VARCHAR(8000) -- main query
DECLARE @v_columns VARCHAR(8000) -- columns
SET @v_columns =''
-- Get string columns
SELECT @v_columns += '[' + CONVERT(VARCHAR, Item) +'],' FROM (SELECT DISTINCT Item FROM #temp2) AS temp
-- Delete the last comma
SET @v_columns = LEFT(@v_columns,LEN(@v_columns)-1)
-- Main query
SET @v_query = 'SELECT Name, ' + @v_columns +' FROM
(
SELECT Name, Item FROM #temp2
) T
PIVOT
(
Count(Item)
FOR Item IN ('+ @v_columns +')
) PVT'
EXEC (@v_query)