【SQL】行列互换代码实例



    对数据表中的行列进行互换,实际开发中用到的一个技巧。直接通过sql语句实现,比起先用sql获取数据再通过其他代码进行行列转换要方便很多。

    看到一段很好的参考代码,摘录学习:



    /*
    普通行列转换
    (爱新觉罗.毓华 2007-11-18于海南三亚)
    
    假设有张学生成绩表(tb)如下:
    Name Subject Result
    张三 语文  74
    张三 数学  83
    张三 物理  93
    李四 语文  74
    李四 数学  84
    李四 物理  94
    */
    
    -------------------------------------------------------------------------
    /*
    想变成
    姓名 语文 数学 物理
    ---------- ----------- ----------- -----------
    李四 74 84 94
    张三 74 83 93
    */
    
    create table tb
    (
    Name varchar(10) ,
    Subject varchar(10) ,
    Result int
    )
    
    insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
    insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
    insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
    insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
    insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
    insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
    go
    
    --静态SQL,指subject只有语文、数学、物理这三门课程。
    select name 姓名,
    max(case subject when '语文' then result else 0 end) 语文,
    max(case subject when '数学' then result else 0 end) 数学,
    max(case subject when '物理' then result else 0 end) 物理
    from tb
    group by name
    /*
    姓名 语文 数学 物理
    ---------- ----------- ----------- -----------
    李四 74 84 94
    张三 74 83 93
    */
    
    --动态SQL,指subject不止语文、数学、物理这三门课程。
    declare @sql varchar(8000)
    set @sql = 'select Name as ' + '姓名'
    select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
    from (select distinct Subject from tb) as a
    set @sql = @sql + ' from tb group by name'
    exec(@sql)
    /*
    姓名 数学 物理 语文
    ---------- ----------- ----------- -----------
    李四 84 94 74
    张三 83 93 74
    */
    
    -------------------------------------------------------------------
    /*加个平均分,总分
    姓名 语文 数学 物理 平均分 总分
    ---------- ----------- ----------- ----------- -------------------- -----------
    李四 74 84 94 84.00 252
    张三 74 83 93 83.33 250
    */
    
    --静态SQL,指subject只有语文、数学、物理这三门课程。
    select name 姓名,
    max(case subject when '语文' then result else 0 end) 语文,
    max(case subject when '数学' then result else 0 end) 数学,
    max(case subject when '物理' then result else 0 end) 物理,
    cast(avg(result*1.0) as decimal(18,2)) 平均分,
    sum(result) 总分
    from tb
    group by name
    /*
    姓名 语文 数学 物理 平均分 总分
    ---------- ----------- ----------- ----------- -------------------- -----------
    李四 74 84 94 84.00 252
    张三 74 83 93 83.33 250
    */
    
    --动态SQL,指subject不止语文、数学、物理这三门课程。
    declare @sql1 varchar(8000)
    set @sql1 = 'select Name as ' + '姓名'
    select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
    from (select distinct Subject from tb) as a
    set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
    exec(@sql1)
    /*
    姓名 数学 物理 语文 平均分 总分
    ---------- ----------- ----------- ----------- -------------------- -----------
    李四 84 94 74 84.00 252
    张三 83 93 74 83.33 250
    */
    
    drop table tb
    
    ---------------------------------------------------------
    ---------------------------------------------------------
    /*
    如果上述两表互相换一下:即
    
    姓名 语文 数学 物理
    张三 74  83  93
    李四 74  84  94
    
    想变成
    Name Subject Result
    ---------- ------- -----------
    李四 语文 74
    李四 数学 84
    李四 物理 94
    张三 语文 74
    张三 数学 83
    张三 物理 93
    */
    
    create table tb1
    (
    姓名 varchar(10) ,
    语文 int ,
    数学 int ,
    物理 int
    )
    
    insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
    insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)
    
    select * from
    (
    select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
    union all
    select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
    union all
    select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
    ) t
    order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end
    
    --------------------------------------------------------------------
    /*加个平均分,总分
    Name Subject Result
    ---------- ------- --------------------
    李四 语文 74.00
    李四 数学 84.00
    李四 物理 94.00
    李四 平均分 84.00
    李四 总分 252.00
    张三 语文 74.00
    张三 数学 83.00
    张三 物理 93.00
    张三 平均分 83.33
    张三 总分 250.00
    */
    
    select * from
    (
    select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
    union all
    select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
    union all
    select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
    union all
    select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
    union all
    select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
    ) t
    order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
    
    drop table tb1

    来源:http://topic.csdn.net/u/20080104/10/11f23e07-a910-485d-bd7b-10acec585e90.html#r_46768732



    本博客所有文章如无特别注明均为原创。
    复制或转载请以超链接形式注明转自枫芸志,原文地址《【SQL】行列互换代码实例
    标签:
    分享:

还没有人抢沙发呢~

无觅相关文章插件,快速提升流量