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