How to do a SELECT inside of a PIVOT I am trying to SELECT values from a column in my database in order to perform a PIVOT on those values without hard-coding them.

SELECT *
FROM (SELECT ItemType, [Status] FROM [EVAULTTEST].[dbo].EvidenceItems WHERE ItemType IS NOT NULL) AS ei

PIVOT(
COUNT([Status])
FOR [Status] 
    IN (SELECT DISTINCT ei.[Status] FROM [EVAULTTEST].[dbo].EvidenceItems AS ei)
) AS piv


However, the SELECT inside of our IN is giving us the error:

Incorrect syntax near 'SELECT'. Expecting '.', ID, or QUOTED_ID.


How can we use a SELECT inside of a FOR IN statement?
This is pretty much what bluefeet gave you as an example in the comments.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME([Status]) 
                    from [EVAULTTEST].[dbo].EvidenceItems
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [ItemType],' + @cols + ' from 
             (SELECT ItemType, [Status]
                from [EVAULTTEST].[dbo].EvidenceItems) x
            pivot 
            (COUNT([Status])
                for [Status] in (' + @cols + ')
            ) p '

execute(@query)