Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Add an order by clause to a dynamic pivot table

Author  Topic 

rolandp
Starting Member

4 Posts

Posted - 2013-04-26 : 17:58:46
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))
from PhdRpt.RptCaseEco
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

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, '')


set @query
= 'select *
from
(
select reportruncaseid, year(Ecodate) as EcoYear, val, col
from phdrpt.rptcaseeco

unpivot
(
val
for col in ('+ @colsUnpivot +')
) u
) x1
pivot
(
max(val)
for ecoyear in ('+ @colspivot +')

) p'


exec(@query)


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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-26 : 18:14:42
At the very end:
..........
pivot
(
max(val)
for ecoyear in ('+ @colspivot +')

) p ORDER BY reportruncaseid'


exec(@query)
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-26 : 18:19:32
If you can't add it at the bottom you may need to make the pivot results a derviced table:
set @query 
= 'select *
from
(
select reportruncaseid, year(Ecodate) as EcoYear, val, col
from phdrpt.rptcaseeco

unpivot
(
val
for col in ('+ @colsUnpivot +')
) u
) x1
pivot
(
max(val)
for ecoyear in ('+ @colspivot +')

) p
ORDER BY reportruncaseid'

or
set @query 
= '
SELCET *
FROM
(

select *
from
(
select reportruncaseid, year(Ecodate) as EcoYear, val, col
from phdrpt.rptcaseeco

unpivot
(
val
for col in ('+ @colsUnpivot +')
) u
) x1
pivot
(
max(val)
for ecoyear in ('+ @colspivot +')

) p
) AS TEMP
ORDER BY reportruncaseid
'
Go to Top of Page

rolandp
Starting Member

4 Posts

Posted - 2013-04-29 : 11:54:59
quote:
Originally posted by James K

At the very end:
..........
pivot
(
max(val)
for ecoyear in ('+ @colspivot +')

) p ORDER BY reportruncaseid'


exec(@query)




Thank you. This worked. However, when I try to take out the C.name LIKE 'Net%' filter, it gives me these errors:

Msg 8167, Level 16, State 1, Line 10
The type of column "EcoDate" conflicts with the type of other columns specified in the UNPIVOT list.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'reportruncaseid'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Ecodate'.

Do you know what would cause this?



Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-29 : 12:18:37
Replace your last statement ( the "exec(@query)") with "print @query and execute. That will show you the SQL statement that will be executed. Copy that to a query window and try to execute. It should give the same error message that you saw. What it looks like is that you have multiple columns in your unpivot list (and other columns in the unpivot statement put together) that have the same column name. Unpivot syntax does not allow you to do that.

Once you print the query, if you are not able to figure out what the problem is, post the printed out query.
Go to Top of Page

rolandp
Starting Member

4 Posts

Posted - 2013-04-29 : 15:37:24
I figured it out. It was because some columns were not of the same datatype. I replaced it with this C.name NOT IN('ReportRunCaseId', 'ReportId', 'EcoDate') and it works like a charm. Thank you for your help.
Go to Top of Page
   

- Advertisement -