Reputation: 42

SQL column sorting by value

Using T-SQL I'm creating a temp table grid. I need to reorder the columns based on the total of the column starting with the largest.

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   0  24   0   0
Prod B  20   0   0   7   0
Prod C  13   0  20  13   0

Upvotes: 0

Answers (3)

Mikael Eriksson

Reputation: 138960

First of, ---- if a terrible column name but I could think of no better for this so I kept it.

You can build the query dynamically where you sort the columns when you build the query string.

declare @SQL nvarchar(max)

set @SQL = '
select [----]'+
  (
  select ', '+T2.N.value('local-name(.)', 'nvarchar(128)')
  from (
       select DO, MO, BC, NI, SC
       from T
       where [----] = 'Total'
       for xml path(''), type
       ) as T1(X)
  cross apply T1.X.nodes('*') as T2(N)
  order by T2.N.value('.', 'int') desc
  for xml path('')
  )+'
from T'

exec (@SQL)

SQL Fiddle

Update

If you think the XML version of building the dynamic query is a bit complicated and unintuitive you can use this instead, totally void of XML stuff.

declare @SQL nvarchar(max)
declare @Col nvarchar(128)

declare C cursor local fast_forward for
  select U.Col
  from (
       select DO, MO, BC, NI, SC
       from T
       where [----] = 'Total'
       ) as T
  unpivot(Val for Col in (DO, MO, BC, NI, SC)) as U
  order by U.Val desc

set @SQL = 'select [----]'

open C
fetch next from C into @Col
while @@FETCH_STATUS = 0
begin
  set @SQL = @SQL + ',' + @Col
  fetch next from C into @Col
end

close C
deallocate C

set @SQL = @SQL + ' from T'

exec (@SQL)

SQL Fiddle

Upvotes: 8