CODEWITHSUNDEEP
sqlsql-servermerge
PMay 1903
PMay 1903

Reputation: 1143

How to merge or join data which have the same data with SQL

How to merge or join the data which has the same information to one row as image below.

Image

SQL:

SELECT C.[LocationCode]
    ,C.[PartNumber]
    ,C.[WorksOrderNumber]
    ,CONVERT(DECIMAL(18,0), C.[BatchQuantity]) AS 'MAC_Outstanding'
FROM CUSTOMER_MN C
WHERE C.[LocationCode] = 'P1'

SELECT R.[LocationCode]
    ,R.[PartNumber]
    ,R.[WorksOrderNumber]
    ,CONVERT(DECIMAL(18,0), R.[PlannedQuantity] - R.[ActualQuantity]) AS 'ASY_Outstanding'
FROM CUSTOMER_RH R
WHERE R.[LocationCode] = 'P1'

Upvotes: 0

Views: 38

Answers (2)

mehdi lotfi
mehdi lotfi

Reputation: 11581

In order to merge data use UNION ALL:

SELECT C.[LocationCode]
    ,C.[PartNumber]
    ,C.[WorksOrderNumber]
    ,CONVERT(DECIMAL(18,0), C.[BatchQuantity]) AS 'MAC_Outstanding'
FROM CUSTOMER_MN C
WHERE C.[LocationCode] = 'P1'

UNION ALL

SELECT R.[LocationCode]
    ,R.[PartNumber]
    ,R.[WorksOrderNumber]
    ,CONVERT(DECIMAL(18,0), R.[PlannedQuantity] - R.[ActualQuantity]) AS 'ASY_Outstanding'
FROM CUSTOMER_RH R
WHERE R.[LocationCode] = 'P1'

In order to merge data use following query:

Select * 
From (
    SELECT C.[LocationCode]
        ,C.[PartNumber]
        ,C.[WorksOrderNumber]
        ,CONVERT(DECIMAL(18,0), C.[BatchQuantity]) AS 'MAC_Outstanding'
    FROM CUSTOMER_MN C
    WHERE C.[LocationCode] = 'P1')z1

Left Join (
    SELECT R.[LocationCode]
        ,R.[PartNumber]
        ,R.[WorksOrderNumber]
        ,CONVERT(DECIMAL(18,0), R.[PlannedQuantity] - R.[ActualQuantity]) AS 'ASY_Outstanding'
    FROM CUSTOMER_RH R
    WHERE R.[LocationCode] = 'P1'
    )z2 On Z1.LocationCode = Z2.LocationCode, Z1.PartNumber = Z2.PartNumber and Z1.WorkOrderNumber = z2.WorkOrderNumber

Upvotes: 0

Jay Kazama
Jay Kazama

Reputation: 3277

Why not use join?

SELECT C.[LocationCode]
    ,C.[PartNumber]
    ,C.[WorksOrderNumber]
    ,CONVERT(DECIMAL(18,0), C.[BatchQuantity]) AS 'MAC_Outstanding'
    ,CONVERT(DECIMAL(18,0), R.[PlannedQuantity] - R.[ActualQuantity]) AS 'ASY_Outstanding'
FROM CUSTOMER_MN C
JOIN CUSTOMER_RH R ON C.LocationCode = R.LocationCode AND C.Partnumber = R.Partnumber AND C.WorksOrderNumber = R.WorksOrderNumber
WHERE C.[LocationCode] = 'P1'

Upvotes: 2

Related Questions