Rows to column header using dynamic T-SQL

There may be instances where you need to prepare columns for a SELECT statement or for a SELECT ... PIVOT.

Sample Data

CREATE TABLE tbl (
  fruit varchar(10))
 
INSERT INTO tbl (fruit) VALUES ('Apple'), ('Banana'), ('Blackberry'), ('Blueberry')

SQL

DECLARE @columns varchar(150)
 
SELECT @columns = COALESCE(@columns + ',', '') + QUOTENAME(fruit) FROM tbl

We begin by creating a variable named @columns with a varchar(150) data type. We don’t want to use varchar(max) because the size won’t exceed 8,000 bytes. You should adjust the string length of your varchar as needed. We use COALESCE to return non-null fields, build the string and to concatenate a comma. We then use QUOTENAME to add brackets (delimiters) to make the input string a valid delimited identifier. If we were to have duplicate fruits, we would use DISTINCT to return unique rows like so:

SELECT @columns = COALESCE(@columns + ',', '') + QUOTENAME(fruit) 
FROM (SELECT DISTINCT fruit FROM tbl) a

The value of our variable @columns is now [Apple],[Banana],[Blackberry],[Blueberry].

Let’s prepare a SELECT statement using our columns.

DECLARE @sql varchar(max)
 
SET @sql = 'SELECT ' + @columns + ' FROM tbl'
 
EXEC @sql

The query for execution would look like:

SELECT [Apple],[Banana],[Blackberry],[Blueberry] FROM tbl

Lastly, we can add the columns to a SELECT ... PIVOT statement.

DECLARE @sql varchar(max)
 
SET @sql = 'SELECT ..., ' + @columns + '
FROM
    (...) AS ...
PIVOT
    (
     ...
FOR [...] IN (' + @columns + ')
) AS ...'
 
EXEC @sql

The query for execution would look like:

SELECT ..., [Apple],[Banana],[Blackberry],[Blueberry]
FROM
    (...) AS ...
PIVOT
    (
     ...
FOR [...] IN ([Apple],[Banana],[Blackberry],[Blueberry])
) AS ...