SQL 如何在sql中将两行合并为一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15411239/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to merge two rows into one row in sql?
提问by user1882705
I have a table as
我有一张桌子
EmployeeID IndividualPay FamilyPay IsActive
1 200 300 true
1 100 150 false
But I want the output as follows(I want to use this output to inner join with some other table)
但我希望输出如下（我想使用这个输出与其他一些表进行内部连接）
EmployeeID IndPay_IsActive IndPay_IsNotActive FamilyPay_IsActive FamilyPay_IsNotActive
1 200 100 300 150
I have looked into PIVOT, but not sure how to use it in my case.
我已经研究过PIVOT，但不确定如何在我的情况下使用它。
回答by Taryn
This type of transformation is known as a pivot. You did not specify what database you are using but you can use an aggregate function with a CASEexpression in any system:
这种类型的转换称为枢轴。您没有指定您使用的数据库，但您可以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
Depending on your database, if you have access to both the PIVOTand UNPIVOTfunctions, then they can be used to get the result. The UNPIVOTfunction converts the IndividualPayand FamilyPaycolumns into rows. Once that is done, then you can create the four new columns with the PIVOTfunction:
根据您的数据库，如果您可以访问PIVOT和UNPIVOT函数，则可以使用它们来获取结果。该UNPIVOT函数将IndividualPay和FamilyPay列转换为行。完成后，您可以使用以下PIVOT函数创建四个新列：
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
See SQL Fiddle with Demo.
Both give the same result:
两者都给出相同的结果：
| EMPLOYEEID | INDIVIDUALPAY_ISACTIVE | INDIVIDUALPAY_ISNOTACTIVE | FAMILYPAY_ISACTIVE | FAMILYPAY_ISNOTACTIVE |
----------------------------------------------------------------------------------------------------------------
| 1 | 200 | 100 | 300 | 150 |
回答by Raymond Saltrelli
Select
EmployeeID,
Active.IndividualPay As IndPay_IsActive,
Active.FamilyPay As FamilyPay_IsActive,
Inactive.IndividualPay As IndPay_IsNotActive,
Inactive.FamilyPay As FamilyPay_IsNotActive
From
PayTable Active
Join PayTable Inactive On Active.EmployeeID = Inactive.EmployeeId
And Inactive.IsActive = 'false'
Where
Active.IsActive = 'true'
