How to convert Pivot into Dynamic Pivot?

Question

I’m wondering how to make the following Pivot into a Dynamic Pivot.

 

SELECT
*
FROM
(
SELECT [date_],[issue_id],[cusip],[Factor_value],[factor_label],[Time_Stamp]
FROM [dbo].[TEMP_lacp_factors]
) AS P
PIVOT
(
MAX([factor_value]) FOR [factor_label] IN ([Book / Price (CIQ)],[12M -  1M Price Momentum])
) AS pv

 

 

I found this.

http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/

 

It seems to do exactly what I want (pickup and pivot by multiple field names) but I don’t know how to convert my Pivot to do the same.

 

This has got to be close, but something is definitely off. --Get distinct values of the PIVOT Column

SELECT @ColumnName= ISNULL(@ColumnName + ',','')
       + QUOTENAME([factor_value])
FROM (SELECT DISTINCT [factor_value] FROM [TEMP_lacp_factors]) AS Factors
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
  N'SELECT [date_],[issue_id],[cusip],[Factor_value],[factor_label]'  + @ColumnName + '
    FROM [TEMP_lacp_factors]
    PIVOT(MAX([factor_value])
          FOR [factor_label] IN (' + @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

Any thoughts on this?

 

 

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Answer 1

I tried another method, which seems to work.

DECLARE @query VARCHAR(4000)
DECLARE @years VARCHAR(2000)
SELECT  @years = STUFF(( SELECT DISTINCT'],[' + [factor_label]
            FROM    [TEMP_lacp_factors]
            ORDER BY '],[' + [factor_label]
            FOR XML PATH('')
            ), 1, 2, '') + ']'
SET @query =
'SELECT * FROM
(
    SELECT [date_],[issue_id],[cusip],[Factor_value],[factor_label]
    FROM [TEMP_lacp_factors]
)t
PIVOT (MAX([factor_value]) FOR [factor_label]
IN ('+@years+')) AS pvt'
EXECUTE (@query)

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Answer 2

create table  [TEMP_lacp_factors] (ID int,  [factor_label]  varchar(100), factor_value int)

Insert into [TEMP_lacp_factors] values (1, 'AAA',5),(2,'bbb',6),(3,'a & b',7),(4,'a <&>b',7),(5,'a & b',9)

DECLARE @query VARCHAR(4000)
DECLARE @years VARCHAR(2000)
SELECT  @years = STUFF(( SELECT ','+ quotename([factor_label])
            FROM    [TEMP_lacp_factors]          
			Group by [factor_label]
			ORDER BY  [factor_label]
            FOR XML PATH(''),type).value('.','varchar(max)'),1,1,'') 
SET @query =
'SELECT * FROM
(    SELECT  [Factor_value],[factor_label]
    FROM [TEMP_lacp_factors]
)t
PIVOT (MAX([factor_value]) FOR [factor_label]
IN ('+@years+')) AS pvt'
EXECUTE (@query)
 


drop table [TEMP_lacp_factors]