当一个系统接近尾声的时候,报表和数据的统计是少不了的。今天正好接到这个任务,做个报表,了解了需求后就开工,用存储过程来实现数据的显示,噼里啪啦的写着写着,突然发现数据的显示不对啊,需旋转90度?想着如果有个旋转函数那就爽啊! Baidu下还总有高手解决此问题,Copy来用用还真管用,不费劲就实现了... 窃喜,得瑟下吧!
数据源如下:
CREATE TABLE TableA(Name VARCHAR(20),Course VARCHAR(20),Grade int)
INSERT INTO TableA SELECT '张三','语文','85'
UNION ALL SELECT '张三','数学','80'
UNION ALL SELECT '张三','英语','60'
UNION ALL SELECT '李四','语文','90'
UNION ALL SELECT '李四','数学','70'
UNION ALL SELECT '李四','英语','80'
UNION ALL SELECT '王五','语文','75'
UNION ALL SELECT '王五','数学','98'
UNION ALL SELECT '王五','英语','55'
现在我们要开始旋转90度了,样式如下:
语文 数学 英语
张三 85 80 60
李四 80 70 80
旋转,转转转:
SELECT [Name],
SUM(CASE Course WHEN '语文' THEN Grade ELSE 0 END) AS 语文,
SUM(CASE Course WHEN '数学' THEN Grade ELSE 0 END) AS 数学,
SUM(CASE Course WHEN '英语' THEN Grade ELSE 0 END) AS 英语
FROM TableA
GROUP BY [Name]
如果课程不能确定?那么上面的代码就不能解决该问题,但问题总有解决的办法,只要你肯探索,幸好有 Baidu + Google ,搜的下就出来了,@@
DECLARE @Sql VARCHAR(8000)
SET @Sql = 'SELECT [Name],'
SELECT @Sql = @Sql + 'SUM(CASE Course WHEN ''' + Course + ''' THEN Grade ELSE 0 END) AS '''+Course+''','
FROM (SELECT DISTINCT Course FROM TableA) AS A
SELECT @Sql = LEFT(@Sql,LEN(@Sql)-1) + ' FROM TableA GROUP BY [Name]'
EXEC (@Sql)
反转,转转转,.先把转横表的数据放到 TableB.
SELECT [Name],'语文' Course, 语文 FROM TableB UNION ALL
SELECT [Name],'数学' Course, 数学 FROM TableB UNION ALL
SELECT [Name],'英语' Course, 英语 FROM TableB ORDER BY [Name],Course DESC
动态旋转,这次安照上面的思路自己搞定,@@
DECLARE @Sql VARCHAR(8000)
SET @Sql = ''
SELECT @Sql = @Sql + 'SELECT [Name],'''+c.name+''' Course, '+c.name+' FROM TableB UNION ALL '
FROM sys.all_columns c
LEFT JOIN sys.all_objects o
ON c.object_id = o.object_id
WHERE O.name = 'TableB' AND C.column_id>1
SET @Sql = LEFT(@Sql,LEN(@Sql) - 9) + ' ORDER BY [Name],Course DESC'
EXEC (@Sql)
横竖表互转完成,正想得瑟的时候,不了我们PMGG过来又要我立马去修改其他重要Bug... = ="