none
Insert results of Pivot Query to table

    Question

  • All -

    I have a dynamic pivot query in which I would like to send the results into a table. The preferred option is to generate the table from a select * into... but I'm having an issue with getting it to do so. I've included my pivot query, so any and all help is appreciated.

    DECLARE

     

    @sql VARCHAR(max)

    DECLARE

     

    @ColumnList VARCHAR(max)

    SELECT

     

    @ColumnList = STUFF(( SELECT DISTINCT

    '],['

     

    + ltrim(convert(varchar,actual_date,101))

    FROM

     

    Call_analysis

    ORDER

     

    BY '],[' + ltrim(convert(varchar,actual_date,101))

    FOR

     

    XML PATH('')

    ),

     

    1, 2, '') + ']'

    set

     

    @sql =

    'select * from

    (select Business_unit, Hour_of_day, Number_of_calls, actual_date

    from Call_analysis

    )a

    PIVOT (Sum(Number_of_calls) FOR actual_date

    IN ('

     

    +@ColumnList+')

    ) b

    order by 1'

     

    exec(@sql)



    Big Ern
    Thursday, March 11, 2010 4:57 PM

Answers

  • Did you try to use SELECT...INTO?

    SET @sql = 'SELECT Business_unit, Hour_of_day, ' +  @ColumnList + 
    ' INTO Results FROM
    (SELECT Business_unit, Hour_of_day, Number_of_calls, actual_date
     FROM Call_analysis
    ) AS a
    PIVOT (SUM(Number_of_calls) FOR actual_date
    IN (' + @ColumnList + ') ) AS b
    ORDER BY Business_unit';

    Plamen Ratchev
    • Marked as answer by BigErn782 Thursday, March 11, 2010 5:15 PM
    Thursday, March 11, 2010 5:10 PM
    Moderator

All replies

  • Did you try to use SELECT...INTO?

    SET @sql = 'SELECT Business_unit, Hour_of_day, ' +  @ColumnList + 
    ' INTO Results FROM
    (SELECT Business_unit, Hour_of_day, Number_of_calls, actual_date
     FROM Call_analysis
    ) AS a
    PIVOT (SUM(Number_of_calls) FOR actual_date
    IN (' + @ColumnList + ') ) AS b
    ORDER BY Business_unit';

    Plamen Ratchev
    • Marked as answer by BigErn782 Thursday, March 11, 2010 5:15 PM
    Thursday, March 11, 2010 5:10 PM
    Moderator
  • Yes, I did, but apparently I didn't have my syntax correct as yours worked.

    Thanks Plamen
    Big Ern
    Thursday, March 11, 2010 5:16 PM