sql - 交换行和列

Question

我已共享示例查询和预期结果。我查询的结果就像

查询这个

select *
into #res
from (
  select 'B1' branch,123 amount,2 count,1234 Total
  union all
  select 'B2' branch,523 amount,23 count,123 Total
  union all
  select 'B3' branch,666 amount,9 count,652 Total
  union all
  select 'B4' branch,234 amount,12 count,256 Total
) res

select * from #res

预期结果图片

我尝试使用枢轴但我没有得到。

标签： sqlsql-serversql-server-2012sql-server-2014

Answer 1

如果只有你branch是动态的，你可以联合amount, count and total.

select * into #res from (
    select 'B1' branch,123 amount,2 count,1234 Total
    union all
    select 'B2' branch,523 amount,23 count,123 Total
    union all
    select 'B3' branch,666 amount,9 count,652 Total
    union all
    select 'B4' branch,234 amount,12 count,256 Total
    union all
    select 'B5' branch,233 amount,12 count,256 Total
)res


declare  @cols nvarchar(max);

declare  @sql nvarchar(max);

select @cols =
    stuff((select N'],[' + branch
       from (select branch
          from #res) AS t1   
       for xml path('')
    ), 1, 2, '') + N']';


set @sql = N'Select ''desc'' as [desc], ' + @cols + N'
            from (select branch from #res)t1 
            pivot 
            (
                max(t1.branch)
                for t1.branch in (' + @cols + N')
            ) p
            union all
            Select ''amount'' as [desc], ' + @cols + N'
            from (select cast(amount as varchar(30)) as amount, branch from #res)t1 
            pivot 
            (
                max(t1.amount)
                for t1.branch in (' + @cols + N')
            ) p
            union all
            Select ''count'' , ' + @cols + N'
            from (select cast([count] as varchar(30)) as [count], branch from #res)t1 
            pivot 
            (
                max(t1.[count])
                for t1.branch in (' + @cols + N')
            ) p
            union all
            Select ''Total'' , ' + @cols + N'
            from (select cast([Total] as varchar(30)) as [Total], branch from #res)t1 
            pivot 
            (
                max(t1.[Total])
                for t1.branch in (' + @cols + N')
            ) p
            '

print @sql;
exec sp_executesql @sql;

drop table #res