locked
How to convert Pivot into Dynamic Pivot? RRS feed

  • 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