How can I have flexible PIVOT column names? Following the example in https://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/, I have a PIVOT that works but is not very flexible. It is basically

SELECT * FROM
(SELECT
...) AS raw
PIVOT (
AGGREGATE(aColumn)
FOR x IN ( [c1], [c2], ...) AS pvt;


But the columns I want in my pivot table are listed in another table.  What I'd really like to do is something like:

SELECT * FROM
(SELECT
...) AS raw
PIVOT (
AGGREGATE(aColumn)
FOR x IN ( (SELECT cName FROM otherTable) ) AS pvt;


But SELECT there ends up being a syntax error.  Is there any work around for this?  I really don't want to have to edit this query every time I add another field that should be a column in the output.
You'll need dynamic SQL for this. Something like (untested with your columns and queries because you left those pretty vague):

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @columns = N'';

SELECT @columns += N', ' + QUOTENAME(cName) FROM dbo.OtherTable;

SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  ...
) AS raw
PIVOT
(
  AGGREGATE(aColumn) FOR x IN ('
  + STUFF(@columns, 1, 1, '')
  + ')
) AS p;';

PRINT @sql;

EXEC sys.sp_executesql @sql;


Note that this is adapted from an article of mine on the same site as the link from your question:


Script to create dynamic PIVOT queries in SQL Server