在SQL中,是否可以按列而不是行进行排序?我不需要基本的ORDER BY语句,我知道它们是如何工作的(例如:按列1、列2等排序)。
基本上是想要对这样的内容进行排序:
column 1 column 2 column 3
1 0 3
尝试对此进行排序:
column 3 column 1 column 2
3 1 0
这在SQL中是否可能?最好使用T-SQL或任何可在SQL Server 2005上运行的内容。
我已经在网上搜索了几个小时,甚至似乎没有人想要提出这个问题,或者是我不擅长搜索。
; with numbered as
(
select SUM(OrderReceived) as c1, SUM(OrderOnHold) as c2, SUM(OrderConfirmed) as c3,
row_number() over (order by employee) RecordNumber
from( SELECT
e.FirstName+' '+e.LastName AS Employee
,CASE WHEN oim.MilestoneID = 10 THEN 1 ELSE 0 END as OrderReceived
,CASE WHEN oim.MilestoneID = 15 THEN 1 ELSE 0 END as OrderOnHold
,CASE WHEN oim.MilestoneID = 20 THEN 1 ELSE 0 END as OrderConfirmed
FROM OrderItems oi
JOIN Orders o on o.orderid = oi.orderid
JOIN OrderItemMilestones oim on oim.orderid = oi.orderid and oim.orderitemid = oi.orderitemid
JOIN Milestones m on m.milestoneid = oim.milestoneid
JOIN Employees e on e.username = oim.recordedbyuser
JOIN Clients cl on cl.clientid = o.clientid
WHERE oim.MilestoneDate Between '2012-08-01' and '2012-08-05'
and e.terminationdate is null
),
ordered as
(
select SUM(OrderReceived) as c1, SUM(OrderOnHold) as c2, SUM(OrderConfirmed) as c3,
row_number() over (partition by RecordNumber
order by employee desc) rn
from numbered
unpivot (v for c in (c1, c2, c3)) u
)
select RecordNumber,
[1] c1,
[2] c2,
[3] c3
from
(
select RecordNumber,
v,
Rn
from ordered
) o
pivot (min(employee) for Rn in ([1], [2], [3])) p