Answered by:
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.
- Edited by ryguy72 Thursday, January 29, 2015 7:47 PM
Thursday, January 29, 2015 7:45 PM
Answers
-
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]
- Marked as answer by ryguy72 Tuesday, February 3, 2015 1:46 AM
Thursday, January 29, 2015 8:23 PM
All replies
-
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.
- Proposed as answer by Eric__Zhang Saturday, January 31, 2015 7:16 AM
Thursday, January 29, 2015 8:11 PM -
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]
- Marked as answer by ryguy72 Tuesday, February 3, 2015 1:46 AM
Thursday, January 29, 2015 8:23 PM