我有一张桌子
EmployeeID IndividualPay FamilyPay IsActive
1 200 300 true
1 100 150 false
但我希望输出如下(我想使用此输出与其他表的内连接)@H_502_5@
EmployeeID IndPay_IsActive IndPay_IsNotActive FamilyPay_IsActive FamilyPay_IsNotActive
1 200 100 300 150
我调查了PIVOT,但不知道如何在我的情况下使用它.@H_502_5@
解决方法
这种类型的转换称为枢轴.您没有指定正在使用的数据库,但您可以在任何系统中使用带有CASE表达式的聚合函数：
select employeeid,max(case when IsActive = 'true' then IndividualPay end) IndPay_IsActive,max(case when IsActive = 'false' then IndividualPay end) IndPay_IsNotActive,max(case when IsActive = 'true' then FamilyPay end) FamilyPay_IsActive,max(case when IsActive = 'false' then FamilyPay end) FamilyPay_IsNotActive from yourtable group by employeeid
见SQL Fiddle with Demo@H_502_5@
根据您的数据库,如果您有权访问PIVOT和UNPIVOT函数,则可以使用它们来获取结果. UNPIVOT函数将IndividualPay和FamilyPay列转换为行.完成后,您可以使用PIVOT函数创建四个新列：@H_502_5@
select *
from
(
select employeeid,case when isactive = 'true'
then col+'_IsActive'
else col+'_IsNotActive' end col,value
from yourtable
unpivot
(
value
for col in (IndividualPay,FamilyPay)
) unpiv
) src
pivot
(
max(value)
for col in (IndividualPay_IsActive,IndividualPay_IsNotActive,FamilyPay_IsActive,FamilyPay_IsNotActive)
) piv
见SQL Fiddle with Demo.@H_502_5@
两者都给出了相同的结果：@H_502_5@
| EMPLOYEEID | INDIVIDUALPAY_ISACTIVE | INDIVIDUALPAY_ISNOTACTIVE | FAMILYPAY_ISACTIVE | FAMILYPAY_ISNOTACTIVE | ---------------------------------------------------------------------------------------------------------------- | 1 | 200 | 100 | 300 | 150 |
