sql中 列转行 博客分类： 数据库 SQL脚本CC++C#

列转行，主要是通过UNION ALL ,MAX来实现。假如有下面这么一个表

代码CREATETABLEProgrectDetail
(
ProgrectName NVARCHAR(20),--工程名称
OverseaSupplyINT,--海外供应商供给数量
NativeSupply INT,--国内供应商供给数量
SouthSupplyINT,--南方供应商供给数量
NorthSupply INT--北方供应商供给数量
)

INSERTINTOProgrectDetail
SELECT'A',100,200,50,50
UNIONALL
SELECT'B',200,300,150,150
UNIONALL
SELECT'C',159,400,20,320
UNIONALL
SELECT'D',250,30,15,15

我们可以通过下面的脚本来实现，查询结果如下图所示

代码SELECTProgrectName,'OverseaSupply'ASSupplier,
MAX(OverseaSupply)AS'SupplyNum'
FROMProgrectDetail
GROUPBYProgrectName
UNIONALL
SELECTProgrectName,'NativeSupply'ASSupplier,
MAX(NativeSupply)AS'SupplyNum'
FROMProgrectDetail
GROUPBYProgrectName
UNIONALL
SELECTProgrectName,'SouthSupply'ASSupplier,
MAX(SouthSupply)AS'SupplyNum'
FROMProgrectDetail
GROUPBYProgrectName
UNIONALL
SELECTProgrectName,'NorthSupply'ASSupplier,
MAX(NorthSupply)AS'SupplyNum'
FROMProgrectDetail
GROUPBYProgrectName


用UNPIVOT 实现如下：

代码SELECTProgrectName,Supplier,SupplyNum
FROM
(
SELECTProgrectName,OverseaSupply,NativeSupply,
SouthSupply,NorthSupply
FROMProgrectDetail
)T
UNPIVOT
(
SupplyNumFORSupplierIN
(OverseaSupply,NativeSupply,SouthSupply,NorthSupply)
)P