CODEWITHSUNDEEP
sqlsortingmultiple-columns
user5469
user5469

Reputation: 19

Sql server sorting field values

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

Upvotes: 1

Views: 171

Answers (1)

sgeddes
sgeddes

Reputation: 62861

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

Upvotes: 1

Related Questions