--============================================== 作者：王运亮(wwwwgou) 时间：2011-06-10 博客：http://blog.csdn.net/wwwwgou --==============================================
--1.行转列 IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp GO CREATE TABLE #temp ( id INT IDENTITY, [name] NVARCHAR(10), [type] NVARCHAR(10), [amount] INT ) INSERT #temp SELECT N'One', N'type1', 1 UNION ALL SELECT N'One', N'type1', 2 UNION ALL SELECT N'One', N'type2', 3 UNION ALL SELECT N'Two', N'type1', 4 UNION ALL SELECT N'Two', N'type1', 5 GO
--#1. 行转列字段值固定. --1.case when SELECT [name], [type1] = SUM(CASE [type] WHEN N'type1' THEN [amount] ELSE 0 END), [type2] = SUM(CASE [type] WHEN N'type2' THEN [amount] ELSE 0 END) FROM #temp GROUP BY [name] --2.pivot SELECT [name], [type1] = ISNULL([type1], 0), [type2] = ISNULL([type2], 0) FROM (SELECT [name], [type], [amount] FROM #temp) a PIVOT (SUM([amount]) FOR [type] IN([type1], [type2])) b
--#2. 行转列字段值不固定,只能拼SQL了. --1.case when DECLARE @sql NVARCHAR(MAX) SET @sql = N''
SELECT @sql = @sql N', ' CHAR(10) QUOTENAME([type]) N' = SUM(CASE [type] WHEN N''' [type] N''' THEN [amount] ELSE 0 END)' FROM (SELECT DISTINCT [type] FROM #temp) T
SET @sql = N'SELECT [name]' @sql SET @sql = @sql CHAR(10) N'FROM #temp GROUP BY [name]'
EXEC(@sql)
--2.pivot SET @sql = N''
SELECT @sql = @sql N', ' CHAR(10) QUOTENAME([type]) N' = ISNULL(' QUOTENAME([type]) N', 0)' FROM (SELECT DISTINCT [type] FROM #temp) T
SET @sql = N'SELECT [name]' @sql SET @sql = @sql CHAR(10) N'FROM (SELECT [name], [type], [amount] FROM #temp) a' CHAR(10) N'PIVOT' CHAR(10) N'(SUM([amount]) FOR [type] IN(' STUFF((SELECT DISTINCT ',' QUOTENAME([type]) FROM #temp FOR XML PATH('')),1,1,'') N')) b'
EXEC(@sql)
--2.列转行 IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp GO CREATE TABLE #temp ( id INT IDENTITY, [name] NVARCHAR(10), [type1] NVARCHAR(10), [type2] NVARCHAR(10), ) INSERT #temp SELECT N'One', 3, 3 UNION ALL SELECT N'Two', 9, 0 GO
--1.union all SELECT rowno=ROW_NUMBER() OVER(ORDER BY name), * FROM ( SELECT name, [type] = N'type1', [amount] = type1 FROM #temp UNION ALL SELECT name, [type] = N'type2', [amount] = type2 FROM #temp ) T
--2.unpivot SELECT rowno=ROW_NUMBER() OVER(ORDER BY name), * FROM (SELECT [name], type1, type2 FROM #temp) a UNPIVOT ([amount] FOR [type] IN([type1],[type2])) b
今天文章到此就结束了，感谢您的阅读好运。
