sort results by column not row

Question

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

Accepted Answer

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

sort results by column not row

Solution

Problem