Greetings experts,
I have a dynamic query that I integrated into a stored procedure.
Here is the query:
CREATE PROCEDURE dbo.uspGetPivotedData
AS
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(d.dateCreated)
FROM DateDetails d
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT employeeName,empTitle,email ' + @cols + ' from
(
select employeeName
, empTitle
,email
, 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
The query works great. The only thing that I would need your help in formatting is how to display dateCreated field as Year.
For instance, instead of April 2019, we would like to just display 2019.
I have no way of testing this, as I don't have your data to test on, but I think this just might work for you:
create procedure dbo.uspGetPivotedData
as
declare @cols as nvarchar(max)
,@vals as nvarchar(max)
,@query as nvarchar(max)
;
set @cols=stuff((select distinct
','
+'['+datepart(year,dateCreated)+']'
from DateDetails
for xml path('')
,type
).value('.', 'nvarchar(max)')
,1
,1
,''
)
;
set @vals=replace(replace(@cols,'[',''),']','');
set @query='select employeeName
,empTitle
,email
'
+@cols
+ ' from (select employeeName
,empTitle
,email
,year(dateCreated) as dateCreated
,sourceincome
from SourceDetails
inner join Employees
on Employees.EmployeeID=SourceDetails.employeeID
inner join DateDetails
on DateDetails.employeeID=SourceDetails.employeeID
) x
pivot (max(sourceincome)
for dateCreated in ('+@vals+')
) p
'
;
execute sp_executesql @query;
I am not sure how this works as written - this query:
Would return a column for every date - as [Jan 11 2019 12:53PM] - not MMM YYYY as in your example unless d.dateCreated is actually stored as a varchar in that format.
If you want this to only display the year - then you need to do a couple of things. First, you need to change this: QUOTNAME(d.dateCreated) to: QUOTENAME(year(d.dateCreated)) - this will generate a list of all possible years from DateDetails table.
Next - you need to modify the query to return the year(dateCreated)...
Then - you need to modify the pivot to use the new column...
CREATE PROCEDURE dbo.uspGetPivotedData
AS
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,'')
set @query = 'SELECT employeeName,empTitle,email ' + @cols + ' from
(
select employeeName
, empTitle
,email
, YearDate = year(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 YearDate in (' + @cols + ')
) p '
EXECUTE sp_executesql @query