oracle sql列转行_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