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
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)
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)
If is is a temp table you could:
But I still have to ask why?
You do know you can order the columns in a select statement?
Do do know how to sum the columns?
select sum(col1), sum(col2)
from #temp
Unfortunately you cannot reorder columns in SQL.
---- DO MO BC NI SC) values or column names?