locked
How do I insert pivot data into a table? RRS feed

  • Question

  • User1216627406 posted

    Greetings again experts,

    The following stored proc contains dynamic query that creates pivot data.

    Here is the code:

    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);
    SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(YEAR(d.dateCreated)) 
                FROM DateDetails d
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    select @cols
    
    set @query = 'SELECT employeeName,empTitle,email ' + @cols + ' from 
                (
                    select employeeName
                        , empTitle
                        ,email
                        , YEAR(dateCreated) as dateCreated
    					,sourceincome
                    from SourceDetails 
    inner join Employees on SourceDetails.employeeID = Employees.EmployeeID
    inner join DateDetails on SourceDetails.employeeID = DateDetails.employeeID
              ) x
                pivot 
                (
                    max(sourceincome)
                    for dateCreated in (' + @cols + ')
                ) p '
                EXECUTE sp_executesql @query

    Is there a way to insert the pivot data from this query into a table so that I will be to grab the data from this table with simple query?

    Thanks in advance

    Saturday, January 12, 2019 5:37 PM

Answers

  • User1216627406 posted

    Never mind, I solved it by modifying this line

    set @query = 'SELECT employeeName,empTitle,email ' + @cols + ' from 

    to

    set @query = 'SELECT employeeName,empTitle,email ' + @cols + ' INTO myNewTable from …

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 12, 2019 6:25 PM