sql 列名和行名怎么互转

Question

类似这种

我可以查出来第一个表那种的查不出来结果表那种 也转不明白 case when 转不成

Answer 1

使用行转列，根据表结构和值动态生成

给你个mssql简单的你可以参考下

select * from master..spt_values where type='p' and number<10

;with t as (
    select * from (
        select convert(varchar(5),name) as name
        ,convert(varchar(5),number) as number
        ,convert(varchar(5),type) as type
        ,convert(varchar(5),low) as low
        ,convert(varchar(5),high) as high
        ,convert(varchar(5),status) as status 
        from master..spt_values 
        where type='p' and number<10
    ) a
    unpivot(val for col in(name,type,low,high,status)) b
)
select * from t
pivot(max(val) for number in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9])) b