Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to merge two rows into one row in sql?

Tags:

sql

pivot

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.

like image 549
user1882705 Avatar asked Mar 14 '13 13:03

user1882705


People also ask

How do I combine multiple rows into one row?

To merge two or more rows into one, here's what you need to do: Select the range of cells where you want to merge rows. Go to the Ablebits Data tab > Merge group, click the Merge Cells arrow, and then click Merge Rows into One.

How do I merge two rows in a table in SQL?

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.

How do I combine multiple rows into one column in SQL?

In this case, we use GROUP_CONCAT function to concatenate multiple rows into one column. GROUP_CONCAT concatenates all non-null values in a group and returns them as a single string. If you want to avoid duplicates, you can also add DISTINCT in your query.

How do I merge rows in MySQL?

To merge rows in MySQL, use GROUP_CONCAT().


2 Answers

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 |
like image 157
Taryn Avatar answered Oct 05 '22 19:10

Taryn


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'
like image 20
Raymond Saltrelli Avatar answered Oct 05 '22 20:10

Raymond Saltrelli