Hello. I have this dynamic pivot table that both pivots and unpivots data and I need to order my column by reportruncaseId.
DECLARE @colsPivot AS NVARCHAR(MAX),
@colsUnpivot as NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @colsPivot = STUFF((SELECT distinct ',' + QUOTENAME(year(EcoDate))
FOR XML PATH(''), TYPE
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('PhdRpt.RptCaseEco') and
C.name LIKE 'Net%'
for xml path('')), 1, 1, '')
= 'select *
select reportruncaseid, year(Ecodate) as EcoYear, val, col
for col in ('+ @colsUnpivot +')
for ecoyear in ('+ @colspivot +')
And here is the result:
I do not know where I can put the order by clause so that it does not give me an error. Thank you in advance for your help.