CODEWITHSUNDEEP
sqlpivotunpivot
Selrac
Selrac

Reputation: 2293

SQL order pivot result by month

I need to order the rows based on a month or order rather than alphabecially.

When I run the query below I get the following result

Col Mon Tue Wed Thu Fri
--- --- --- --- --- ---
Feb 1   2   3   4   5
Jan 2   3   4   5   6 
Mar 3   4   5   6   7

How can I order it as follows:

Col Mon Tue Wed Thu Fri
--- --- --- --- --- ---
Jan 2   3   4   5   6 
Feb 1   2   3   4   5
Mar 3   4   5   6   7

My query:

select *
from
(
  select LEFT(datename(dw,datetime),3) as DateWeek,
    col, 
    value
  from DataTable
  cross apply 
  (
    values ('Jan', JData), ('Feb', FData),  ('Mar', MData)
  ) c (col, value)
) src
pivot
(
  sum(value)
  for dateweek in (Mon, Tue, Wed, Thu, Fri)
) piv

At the moment I'm replacing the value's line as:

values ('1.Jan', JData), ('2.Feb', FData),  ('3.Mar', MData)

I guess there is a better solution

Upvotes: 1

Views: 245

Answers (2)

Selrac
Selrac

Reputation: 2293

Thanks Wawrzyniec Sz., I figure it out the solution. Adding an additional column does the trick.

For months the code will be as follows:

select col, Mon, Tue, Wed, Thu, Fri
from
(
  select LEFT(datename(dw,datetime),3) as DateWeek, 
    ord,
    col, 
    value
  from DataTable
  cross apply 
  (
    values ('1','Jan', AVol_Offered), ('2','Feb', OVol_Offered),  ('3','Mar', AAHT)
  ) c (ord, col, value)
) src
pivot
(
  sum(value)
  for dateweek in (Mon, Tue, Wed, Thu, Fri)
) piv
order by ord

This would apply to other values, for example, if I want to maintain the order listed as C,A,B (which you can not order it asc or des) you will can also use the same code as follows:

select col, Mon, Tue, Wed, Thu, Fri
from
(
  select LEFT(datename(dw,datetime),3) as DateWeek, 
    ord,
    col, 
    value
  from DataTable
  cross apply 
  (
    values ('1','C', AVol_Offered), ('2','A', OVol_Offered),  ('3','B', AAHT)
  ) c (ord, col, value)
) src
pivot
(
  sum(value)
  for dateweek in (Mon, Tue, Wed, Thu, Fri)
) piv
order by ord

Thanks for the help/hints

Upvotes: 1

www
www

Reputation: 4391

Try this:

select * from DataTable
order by convert(datetime, '2012/'+ Col +'/01')

SQLFiddle

Upvotes: 1

Related Questions