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

### 3 Solutions collect form web for “sort results by column not row”

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

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

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

- Query your data to get values of columns

a)**And**generate dynamic SQL with column order as per their values from result - 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.