This website requires JavaScript.

行列转换小结 Pivot ,Unpivot (转,改)

行专列 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 , [1] , [2] , [3] FROM    Sales.Orders PIVOT( SUM(freight) FOR shipperid IN ( [1], [2], [3] ) ) 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 ([1],[2],[3]) ) AS P;

UNPIVOT
SELECT  *
FROM    Sales.FreightTotals;
-- unpivot data
SELECT  custid ,
        shipperid ,
        freight
FROM    Sales.FreightTotals UNPIVOT( freight FOR shipperid IN ( [1], [2], [3] ) ) AS U;
[![image](http://images.cnitblog.com/blog/378031/201503/112237489333925.png "image")](http://images.cnitblog.com/blog/378031/201503/112237482453081.png)
**动态**
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')
### 参考文档
[03\. 行列转换写法小结](http://www.cnblogs.com/seusoftware/p/3238022.html)
0条评论
avatar