SQL列转行及行转列

--==============================================
作者：王运亮(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