SQL - Pivot row values as column names in select statement (Concatenate row values)

QUOTENAME()

Return a Unicode string with bracket delimiters (default):

or SELECT QUOTENAME('abcdef', '()') returns (abcedf)

FOR XML PATH(''), TYPE

converts these rows into a single strongly-typed XML text node with the concatenated rows.

Adding FOR XML PATH to the end of a query allows you to output the results of the query as XML elements, with the element name contained in the PATH argument. For example, if we were to run the following statement:

SELECT ',' + name 
              FROM temp1
              FOR XML PATH ('')

By passing in a blank string (FOR XML PATH('')), we get the following instead:

,aaa,bbb,ccc,ddd,eee

Invoking the method value('.', 'NVARCHAR(MAX)') on that XML node converts the XML node to an nvarchar(MAX) string. 

STUFF()

Remove leading comma with STUFF.

The STUFF statement literally "stuffs” one string into another, replacing characters within the first string. We, however, are using it simply to remove the first character of the resultant list of values.

SELECT abc = STUFF((
            SELECT ',' + NAME
            FROM temp1
            FOR XML PATH('')
            ), 1, 1, '')
FROM temp1

So we end up with:

aaa,bbb,ccc,ddd,eee

Example:

IF OBJECT_ID('dbo.tbl_XXXX','U') IS NOT NULL DROP TABLE dbo.tbl_tbl_XXXX

DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX) ; SET @cols = STUFF(( SELECT ',' + QUOTENAME(data_refresh_date) FROM dbo.tbl_some_source GROUP BY data_refresh_date order by data_refresh_date FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

set @query = 'SELECT state,Play,attribute, metric_level,' + @cols + ' from ( SELECT DISTINCT A.state,A.Play,A.attribute,A.metric_level, A.data_refresh_date,

CONVERT(varchar,A.cnt*100/B.cnt,2) + ''%'' perc

FROM dbo.tbl_some_source A INNER JOIN dbo.tbl_some_source B ON A.State = B.State AND A.Play = B.play AND B.data_refresh_date = A.data_refresh_date AND B.attribute = ''API_Count'' ) x pivot ( max(perc) for data_refresh_date in (' + @cols + ') ) p '

execute('SELECT * INTO dbo.tbl_destination FROM (' + @query + ')x')

Explanation:

The xml part returns an xml result as:

Then by using dynamic SQL, we can use the result as column names to pivot data:

Other reference:

https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/