按列而不是按行对结果进行排序

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

这是对列进行排序的相对简单的方法。如果你先unpivot，排序和pivot您的数据，您将获得排序的列。

这是 Sql Fiddle 示例.

-- Assign arbitrary numbers to records
-- You might skip this part if you have unique column
-- in which case you should replace RecordNumber with this ID
; with numbered as
(
  select *,
         row_number() over (order by (select null)) RecordNumber
    from test
),
-- Generate order by
-- For all the columns in record.
-- Rn will always be in range
-- 1..NumberOfColumns
-- Order is done on unpivoted data
ordered as
(
  select *,
         row_number() over (partition by RecordNumber
                            order by v desc) rn
    from numbered
 -- list all the columns here
 -- v is for value
 -- c is for column
 unpivot (v for c in (c1, c2, c3)) u
)
-- Finally return the data in original layout
select RecordNumber,
       [1] c1,
       [2] c2,
       [3] c3
  from 
  (
    -- Only the columns needed by the query
    -- Pivot will not play nice even if you
    -- Select only subset of columns in
    -- outer query
    select RecordNumber,
           v,
           Rn
      from ordered
  ) o
 -- Get value for row numbers 1..NumberOfColumns
 pivot (min(v) for Rn in ([1], [2], [3])) p

您可能想要添加标题行，以便知道哪个值来自哪一列。为此，我将添加一个标识标题/行的列，将所有内容合并到o获取相应的标题和顺序，以确保这两行保持在一起：

  (
    select RecordNumber,
           v,
           Rn,
           1 HdrRow
      from ordered
     union all
    select RecordNumber,
           c,    -- Column name is in c
           Rn,
           0 HdrRow
      from ordered
  ) o
    ...
  order by RecordNumber, HdrRow