How to ORDER BY in SQL PIVOT

pivot, sql, sql-server, t-sql

Solution

Try this:

with Mth (st, nd) as ( 
  select DATEADD (M, datediff (m, 0,'2012-09-01'), 0), 
         DATEADD (M, DATEDIFF (m, 0, '2012-09-01') + 1, 0)   
  union all 
  select DATEADD (m, 1, st), 
         DATEADD (m, 1, nd) 
  from Mth 
  where nd <= DATEADD (m, datediff (m, 0, getdate()), 0)
), Pivoted
AS
(     
    select * 
    from 
    ( 
      select MONTH(Mth.st) Month, 
          U.USER, 
          COUNT(S.QRY_ID) Searches 
      FROM Mth 
      LEFT JOIN SEARCHES S 
        on Mth.st <= S.CREATED 
        and Mth.nd > S.CREATED 
      LEFT JOIN MEMBERS U 
        on U.AID = S.AID 
      GROUP BY YEAR(Mth.st), MONTH(Mth.st), U.HOLDER_LOGIN
    ) src 
    pivot 
    ( 
      sum(searches) 
      for month in ([12],[11],[10]) 
    ) piv
)
SELECT * 
FROM Pivoted
ORDER BY Dec

Problem

I currently have this query using PIVOT generating a table like this: ``` USER | DEC | NOV | OCT --------------------------------- bob | 3 | 5 | 2 jon | 7 | 0 | 1 tim | 4 | 2 | 6 ``` What I would like to do but it looks like a stretch is to `ORDER BY` the results by the `DEC` value descending. This is the query: ``` with Mth (st, nd) as ( select DATEADD (M, datediff (m, 0,'2012-09-01'), 0), DATEADD (M, DATEDIFF (m, 0, '2012-09-01') + 1, 0) union all select DATEADD (m, 1, st), DATEADD (m, 1, nd) from Mth where nd <= DATEADD (m, datediff (m, 0, getdate()), 0) ) select * from ( select MONTH(Mth.st) Month, U.USER, COUNT(S.QRY_ID) Searches FROM Mth LEFT JOIN SEARCHES S on Mth.st <= S.CREATED and Mth.nd > S.CREATED LEFT JOIN MEMBERS U on U.AID = S.AID GROUP BY YEAR(Mth.st), MONTH(Mth.st), U.HOLDER_LOGIN ) src pivot ( sum(searches) for month in ([12],[11],[10]) ) piv ``` Doing `piv ORDER BY piv.Searches` gives an error so is it possible to specify the column?

Original source