How do I insert pivot data into another table?

Transact-SQL
1

Greetings again,

The following stored procedure contains dynamic query that creates pivot table with data.

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

@yosiasz, a contributor here was generous enough to assist with a part of the code I was having problem with.

When I execute this stored proc, it produces the correct data.

I do have one serious problem that I have not been able to figure out how to approach/.

I want to insert the pivoted data into a table so I can query the records from this table.

Any ideas how to approach this?

If it helps to accomplish this with the dynamic query without stored procedure, that would be greatly appreciated.

Many thanks in advance

2

Modify this line to be

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

3

Thank you @mike01.

This is one of those solutions that makes me look stupid.

I tried this solution before posting here, using #temp table but when I refreshed db, I don't see the table.

This time, your solution worked.

Thanks a lot for your help

4

What do you mean refreshed db? If you are looking for it in SSMS list, it won't show. It is a temp table that you query like select * from #Temp. It resides in tempdb. We use temp tables here as examples. Once the session is closed, the temp tables created in that session are also deleted