行转列,老生常谈的问题。这里总结一下网上的方法。
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语句生成自动行转列的脚本。好动手的童鞋赶快动起来吧。
如果您有疑问,欢迎评论区交流讨论
笑声激发自强,发愤、图强、飞身向上