pivot – How to merge two rows into one row in sql?

pivot – How to merge two rows into one row in sql?

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 CASE expression in any system:

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

See SQL Fiddle with Demo

Depending on your database, if you have access to both the PIVOT and UNPIVOT functions, then they can be used to get the result. The UNPIVOT function converts the IndividualPay and FamilyPay columns into rows. Once that is done, then you can create the four new columns with the PIVOT function:

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 |
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

pivot – How to merge two rows into one row in sql?

Leave a Reply

Your email address will not be published.