冲扬心法

笑声激发自强,发愤、图强、飞身向上

   :: 首页  ::  ::  ::  :: 管理

行转列,老生常谈的问题。这里总结一下网上的方法。

1、生成测试数据:

CREATE TABLE human(
    name NVARCHAR(5),    --姓名
    norm NVARCHAR(5),    --指标
    score INT ,            --分数
    grade NVARCHAR(2)    --等级
)
GO
INSERT INTO human(name,norm,score,grade)VALUES
('旺仔','考勤',56,'c'),
('旺仔','生产',85,'b'),
('旺仔','技术',95,'a'),
('小杰','考勤',66,'a'),
('小杰','生产',77,'b'),
('小杰','技术',88,'c'),
('玉红','考勤',92,'j'),
('玉红','生产',73,'k'),
('玉红','技术',81,'m')

查询数据:

注意:这里的score是数值类型列,而grade是字符串类型的列

 

2、利用case when 语句完成行转列,其中行转列之后的列的属性是数值类型

SELECT 
    name,
    SUM(CASE WHEN norm = '考勤' THEN score ELSE 0 END) AS 考勤,
    SUM(CASE WHEN norm = '生产' THEN score ELSE 0 END) AS 生产,
    SUM(CASE WHEN norm = '技术' THEN score ELSE 0 END) AS 技术
FROM dbo.human
GROUP BY name

结果:

 

3、利用case when 语句完成行转列,其中行转列之后的列的属性是字符串类型

又分为两种情况,a:是借用for xml path 拼接字符串,b:巧妙的借用max()函数可以对字符串进行运算的特点进行筛选

a:借用for xml path 拼接字符串

SELECT  name ,
        ( SELECT    grade + ''
          FROM      dbo.human
          WHERE     name = a.name
                    AND norm = '考勤'
        FOR
          XML PATH('')
        ) AS 考勤 ,
        ( SELECT    grade + ''
          FROM      dbo.human
          WHERE     name = a.name
                    AND norm = '生产'
        FOR
          XML PATH('')
        ) AS 生产 ,
        ( SELECT    grade + ''
          FROM      dbo.human
          WHERE     name = a.name
                    AND norm = '技术'
        FOR
          XML PATH('')
        ) AS 技术
FROM    dbo.human a
GROUP BY name;

结果:

b:巧妙的借用max()(或min())函数可以对字符串进行运算的特点进行筛选

SELECT  name ,
        MAX( CASE WHEN a.norm = '考勤' THEN a.grade ELSE '' END ) AS 考勤,
        MAX( CASE WHEN a.norm = '生产' THEN a.grade ELSE '' END ) AS 生产,
        MAX( CASE WHEN a.norm = '技术' THEN a.grade ELSE '' END ) AS 技术
FROM    dbo.human a
GROUP BY name;

结果:

 

3、实际生产过程中会碰到这种情况:norm列的值有很多种情况,比如几十、上百个,难道我们一一手写吗?不,我们可以考虑使用拼接字符串的方式,动态实现行转列

DECLARE @sql NVARCHAR(MAX);
SELECT  @sql = 'select name,
';

SELECT  @sql = @sql + 'max(case when a.norm = ''' + a.norm
        + ''' then a.grade ELSE '''' END ' + ') as ' + QUOTENAME(a.norm) + ',
'
FROM    ( SELECT DISTINCT
                    norm
          FROM      dbo.human
        ) a; 

SELECT  @sql = SUBSTRING(@sql, 1, LEN(@sql) - 3);

SELECT  @sql = @sql + '
FROM    dbo.human a
GROUP BY name;';

SELECT  @sql;
EXEC (@sql);

首先观察一下我们自动拼接出来的sql语句:

完美!拼接的语句正式我们所希望的,所以结果也不出所料:

 

4、pivot新特性实现行转列,针对行转列后,列的属性是数值类型的情况,这里指score

SELECT  *
FROM    ( SELECT    name ,
                    norm ,
                    score
          FROM      dbo.human
        ) t PIVOT( SUM(score) FOR norm IN ( 考勤, 生产, 技术 ) ) AS pvt;

结果:

5、pivot新特性实现行转列,针对行转列后,列的属性是字符串类型的情况,这里指score

SELECT  *
FROM    ( SELECT    name ,
                    norm ,
                    grade
          FROM      dbo.human
        ) t PIVOT( MAX(grade) FOR norm IN ( 考勤, 生产, 技术 ) ) AS pvt;

 

6、同理,我们也可以通过拼接字符串的形式来组织pivot语句生成自动行转列的脚本。好动手的童鞋赶快动起来吧。

如果您有疑问,欢迎评论区交流讨论

 

posted on 2018-12-06 22:58  风雨一肩挑  阅读(6242)  评论(0编辑  收藏  举报