行专列 Pivot
1）SQL 2000版本
静态
SELECT ID ,
SUM(CASE Code
WHEN 'Item1' THEN Value
ELSE 0
END) AS Item1 ,
SUM(CASE Code
WHEN 'Item2' THEN Value
ELSE 0
END) AS Item2 ,
SUM(CASE Code
WHEN 'Item3' THEN Value
ELSE 0
END) AS Item3
FROM RowToCol
GROUP BY ID
--或者用max也行
SELECT ID ,
MAX(CASE Code
WHEN 'Item1' THEN Value
ELSE 0
END) AS Item1 ,
MAX(CASE Code
WHEN 'Item2' THEN Value
ELSE 0
END) AS Item2 ,
MAX(CASE Code
WHEN 'Item3' THEN Value
ELSE 0
END) AS Item3
FROM RowToCol
GROUP BY ID
动态
在不确定有多少行需要转为列时，先distinct出待转列的值，再拼出包含若干个CASE的SQL语句，然后运行。
DECLARE @sql VARCHAR(8000)
SET @sql = 'select ID '
SELECT @sql = @sql + ' , max(case Code when ''' + Code
+ ''' then Value else 0 end) [' + Code + ']'
FROM ( SELECT DISTINCT
Code
FROM RowToCol
) AS a
SET @sql = @sql + ' from RowToCol group by ID'
--print @sql
EXEC(@sql)
2） SQL 2005以后版本
PIVOT是SQL Server 2005的新语法，Oracle在11g中也推出了这个语法。
静态
SELECT custid ,
[] ,
[] ,
[]
FROM Sales.Orders PIVOT( SUM(freight) FOR shipperid IN ( [], [], [] ) ) AS P;
分解
结果
动态
用PIVOT拼写动态SQL时就简单了，只要把后面的列清单整理出来就可以了。
DECLARE @sql VARCHAR(8000)
SELECT @sql = ISNULL(@sql + '],[', '') + Code
FROM RowToCol
GROUP BY Code
SET @sql = '[' + @sql + ']'
--print @sql
EXEC ('select * from (select * from RowToCol) a pivot (max(value) for Code in (' + @sql + ')) b')
列转行 UNPIVOT
1）SQL Server 2000版
静态
SELECT ID ,
Code = 'Item1' ,
Value = Item1
FROM ColToRow
UNION ALL
SELECT ID ,
Code = 'Item2' ,
Value = Item2
FROM ColToRow
UNION ALL
SELECT ID ,
Code = 'Item3' ,
Value = Item3
FROM ColToRow
ORDER BY ID
SQL Server对于多个UNION的排序，只要在最后加ORDER BY就可以了。
动态
在不确定有多少列需要转为行时，先借助系统表syscolumns找出待转行的列，再拼出包含若干个UNION语句，然后运行。
DECLARE @sql VARCHAR(8000)
SELECT @sql = ISNULL(@sql + ' union all ', '') + ' select ID , [Code] = '
+ QUOTENAME(name, '''') + ' , [Value] = ' + QUOTENAME(name)
+ ' from ColToRow'
FROM syscolumns
WHERE name <> N'ID'
AND id = OBJECT_ID('ColToRow')
ORDER BY colid ASC
--print @sql
EXEC(@sql + ' order by ID ')
2） SQL 2005以后版本
静态
创建样本
-- sample data for UNPIVOT example
USE TSQL2012;
IF OBJECT_ID(N'Sales.FreightTotals', N'U') IS NOT NULL DROP TABLE Sales.FreightTotals;
GO WITH PivotData AS
(
SELECT
custid , -- grouping column
shipperid, -- spreading column
freight -- aggregation column
FROM Sales.Orders
)
SELECT *
INTO Sales.FreightTotals
FROM PivotData
PIVOT( SUM(freight) FOR shipperid IN ([],[],[]) ) AS P;
UNPIVOT
SELECT *
FROM Sales.FreightTotals;
-- unpivot data
SELECT custid ,
shipperid ,
freight
FROM Sales.FreightTotals UNPIVOT( freight FOR shipperid IN ( [], [], [] ) ) AS U;
动态
DECLARE @sql VARCHAR(8000)
SELECT @sql = ISNULL(@sql + '],[', '') + name
FROM syscolumns
WHERE name <> N'ID'
AND id = OBJECT_ID('ColToRow')
SET @sql = '[' + @sql + ']'
--print @sql
EXEC('select ID , Code , Value from ColToRow unpivot (Value for Code in(' + @sql + ')) t')