sort results by column not row

问题:

is it possible in SQL to sort by column and not by row? i do not need a basic ORDER BY statement, i know how those work (ie: order by column1, column2, etc).

basically trying to sort something like this:

column 1    column 2    column 3
   1            0           3 

trying to sort to this:

column 3    column 1    column 2
   3           1           0 

Is this even possible in SQL? preferably t-sql or anything that will run on sql server 2005

ive been searching online for hours on this and no one even seems to want to ask this question. or i suck at searching.

   ; 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

回答1:

Here is relatively simple way to sort columns. If you first unpivot, sort and pivot your data, you will get sorted columns.

Here is Sql Fiddle with example.

-- 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

You might want to add header rows so you would know which value come from which column. To do this I would add a column identifying header/row, union all to o to get corresponding headers and order by which would ensure that these two rows stay together:

  (
    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

回答2:

Are you using SELECT *? If so, stop doing that. If you know you want them listed that way, then write:

SELECT [column 3], [column 1], [column 2] FROM dbo.table;

If you mean you want to sort within those columns, I'm not sure how meaningful that would be. But I guess you could do something like:

DECLARE @x TABLE(c1 INT, c2 INT, c3 INT);

INSERT @x(c1,c2,c3) SELECT 2,1,3
UNION ALL SELECT 3,4,5
UNION ALL SELECT 5,4,3
UNION ALL SELECT 3,1,2
UNION ALL SELECT 3,3,3
UNION ALL SELECT 3,4,3
UNION ALL SELECT 4,3,4;

SELECT c1 = CASE
  WHEN c1 >= c2 AND c1 >= c3 THEN c1
  WHEN c2 >= c1 AND c2 >= c3 THEN c2
  ELSE c3 END,
c2 = CASE 
  WHEN c1 >= c2 AND c1 >= c3 THEN 
    CASE WHEN c2 >= c3 THEN c2 ELSE c3 END
  WHEN c2 >= c1 AND c2 >= c3 THEN
    CASE WHEN c1 >= c3 THEN c1 ELSE c3 END
  ELSE
    CASE WHEN c1 >= c2 THEN c1 ELSE c2 END
  END,
c3 = CASE
  WHEN c1 <= c2 AND c1 <= c3 THEN c1
  WHEN c2 <= c1 AND c2 <= c3 THEN c2
  ELSE c3 END
FROM @x;

Results:

c1   c2   c3
---- ---- ----
3    2    1
5    4    3
5    4    3
3    2    1
3    3    3
4    3    3
4    4    3

回答3:

Double query with dynamic query

If your query only returns a single row, then you can go about this problem by executing a query two times:

1. Query your data to get values of columns
   a) And generate dynamic SQL with column order as per their values from result
2. Execute dynamic query

If your query should return several rows of data I suppose we're going to need some more information what you're trying to achieve to come up with a feasible solution or at least a better suggestion how to mitigate your problem.