Sorting Pivot results

Andy_L_J

Given the following Query, I get the desired results, however, the result set is sorted on the PLine Column. I would like to Order By the ID Column, or Better yet, the Factory.Factory_No (varChar), ProdnLine.ProdnLine_No (int)columns.

DECLARE @LineKgs Table(
ID Int, PLine VarChar(7),kgs Decimal(18,7), TDate nVarChar(10))

INSERT INTO @LineKgs
SELECT f.Factory_No + '/' + CAST(pl.ProdLine_No As VarChar), pl.ProdLine_ID,
mt.Qty, CAST(month(mt.TransDate) AS VArChar(10))

FROM Production pr
LEFT OUTER JOIN ProdLine pl
ON pl.ProdLine_ID = pr.ProdLine_ID
JOIN MaterialTransaction mt
ON pr.Prodn_ID = mt.Prodn_ID
JOIN Factory f
ON pl.Factory_ID = f.Factory_ID
WHERE mt.TransDate BETWEEN '1/1/2010' AND '12/31/2010'

SELECT ID, PLine, [1] As Jan, [2] As Feb, [3] As Mar, [4] As Apr,
[5] As May, [6] As Jun, [7] As Jul, [8] As Aug,
[9] As Sep, [10] As Oct, [11] As Nov, [12] as Dec
FROM( SELECT ID, PLine, kgs, TDate

  FROM @LineKgs) p2
  PIVOT(SUM(kgs)
     FOR TDate In (\[1\], \[2\],\[3\],\[4\],\[5\],\[6\],
                   \[7\],\[8\],\[9\],\[10\],\[11\],\[12\])) As P

Current Result

ID PLine Jan Feb Mar ...

1 Fac1/1 8497 60012 19503 ...
2 Fac1/2 104772 152379 33795
3 Fac1/3 76338 113653 40742
4 Fac1/4 197339 333348 118314
5 Fac1/5 200986 363314 106225
6 Fac1/6 35048 189619 44345
7 Fac1/7 45632 133981 52921
8 Fac1/8 58387 185468 39916
9 Fac1/9 34625 76026 69479
10 Fac2/1 420 4150 828
19 Fac2/10 10816 25213 2681
20 Fac2/11 13680 30765 4597
21 Fac2/12 10576 NULL 19068
11 Fac2/2 251 333 NULL
12 Fac2/3 433 1536 NULL
...

Desired Result:

ID PLine Jan Feb Mar ...

1 Fac1/1 8497 60012 19503 ...
2 Fac1/2 104772 152379 33795
3 Fac1/3 76338 113653 40742
4 Fac1/4 197339 333348 118314
5 Fac1/5 200986 363314 106225
6 Fac1/6 35048 189619 44345
7 Fac1/7 45632 133981 52921
8 Fac1/8 58387 185468 39916
9 Fac1/9 34625 76026 69479
10 Fac2/1 420 4150 828
11 Fac2/2 251 333 NULL
12 Fac2/3 433 1536 NULL
...
20 Fac2/11 13680 30765 4597
21 Fac2/12 10576 NULL 19068
...

As always, your guidance is most appreciated.

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

Mycroft Holmes

I must be missing something, add an order by to your select after as P

Never underestimate the power of human stupidity RAH

Andy_L_J

Thank you Mycroft, I admit to not reading the docs rigorously :-O

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife