Sort results by non row column

Question

Is it possible in SQL to sort by column rather than row? I do not need the basic ORDER BY operator, I know how it works (for example: order by column1, column2, etc.).

basically trying to sort something like this:

column 1 column 2 column 3 1 0 3 

trying to sort by this address:

 column 3 column 1 column 2 3 1 0 

Is this possible in SQL? preferably t-sql or whatever will work on sql server 2005

ive has been searching the Internet for many hours, and no one even wants to ask this question. or i suck when 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 

Answer 1

Here is a relatively simple way to sort columns. If this is your first univot , collect and collapse your data, you will be sorted in columns.

Here is an Sql Fiddle example with an 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 lines so that you know what value came from this column. To do this, I would add a column defining the header / row, combining everything in o to get the appropriate headers and order to 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 

Answer 2

Are you using SELECT * ? If so, stop doing it. If you know you want them listed in this way, write:

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

If you mean that you want to sort in these columns, I'm not sure how significant this is. But I think 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 

Answer 3

Double query with dynamic query

If your query returns only one row, you can solve this problem by running the query twice:

• Query data to get column values
  a) And creates dynamic SQL with the order of the columns according to their values ​​from the result
• Dynamic query execution

If your query needs to return multiple rows of data, I assume that we will need more information about what you are trying to achieve in order to find an acceptable solution or at least the best suggestion on how to mitigate your problem.