Sql server sorting field values

Question

Is it possible to sort fields left to right in sql server? for example

----    DO  MO  BC  NI  SC
Total   22  44  53  57  24
Prod A  0   24  0   24   0
Prod B  0   0   0   20   7
Prod C  0   20  0   13  13

Would become:

----    NI  BC  MO  SC  DO
Total   57  53  44  24  22
Prod A  24   24  0   0   0
Prod B  20   7   0   0   0
Prod C  20   13  13  0   0

this would ignore the column names which I can update after.

Thanks

I suppose you could do this using Dynamic SQL to build the final query based on evaluating the values in the columns, but why would you want to do this? It'd mean that you potentially have a totally different result set every time the query runs, which would lead to mass confusion for end users and analysts. — SchmitzIT
– SchmitzIT, Commented Nov 8, 2013 at 12:52

sgeddes · Accepted Answer · 2013-11-08 13:26:52Z

1

While I would never recommend using this method (as this seems to be geared towards presentation logic), it did intrigue me to find a way to accomplish the results.

Here's one method using both pivot and unpivot. You lose your column headers (but they aren't accurate any longer after the transformation anyhow):

WITH CTE AS (
  SELECT field, field2, field3
  FROM 
     (SELECT field, do, mo, bc, ni, sc
      FROM yourresults) p
  UNPIVOT
     (field3 FOR field2 IN 
        (do, mo, bc, ni, sc)
     )AS unpvt
  ),
CTE2 AS (
  SELECT field, 
    field3,
    row_number() over (partition by field order by field3 desc) rn
  from cte
  )
SELECT field, [1], [2], [3], [4], [5]
FROM (
  SELECT field, field3, rn
  FROM CTE2) P
PIVOT 
(
  max(field3)
  FOR rn IN
( [1], [2], [3], [4], [5] )
) AS pvt

SQL Fiddle Demo

answered Nov 8, 2013 at 13:26

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user5469 Over a year ago

I knew this works in excel but not in sql.Thanks Guys this is perfect, works like a charm.

Collectives™ on Stack Overflow

Sql server sorting field values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related