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,  c1,  c2,  c3 from ( select RecordNumber, v, Rn from ordered ) o pivot (min(employee) for Rn in (, , )) 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,  c1,  c2,  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 (, , )) 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;
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.