How can I have flexible PIVOT column names?

Question

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.

Answer 1

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