SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Add an order by clause to a dynamic pivot table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rolandp
Starting Member

USA
4 Posts

Posted - 04/26/2013 :  17:58:46  Show Profile  Reply with Quote
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.

Edited by - rolandp on 04/26/2013 17:59:56

James K
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 04/26/2013 :  18:14:42  Show Profile  Reply with Quote
At the very end:
..........
      pivot
      (
        max(val)
        for ecoyear in ('+ @colspivot +')
        
      ) p  ORDER BY reportruncaseid'

      
exec(@query)
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/26/2013 :  18:19:32  Show Profile  Reply with Quote
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

USA
4 Posts

Posted - 04/29/2013 :  11:54:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 04/29/2013 :  12:18:37  Show Profile  Reply with Quote
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

USA
4 Posts

Posted - 04/29/2013 :  15:37:24  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000