CODEWITHSUNDEEP
asp.netsql-server-2008
Vijjendra
Vijjendra

Reputation: 25233

Merge 2 Tables Data in SQL

I have 3 Data Table Claim, Part and Labor.

In this Claim is parent table and Part and Labor is mapping tables of Claim and they have Part and Labor has the ClaimId as a Foreign Key.

Claim has data like:

enter image description here

Part has data Like

enter image description here

Labor table has data Like

enter image description here

Target Output would be:

enter image description here

Can anyone help me to achieve this in SQL server.

I have tried to solve with the Union/CTE but it did not gives the result as I want.

Upvotes: 0

Views: 62

Answers (2)

martennis
martennis

Reputation: 872

I got the same output (for your updated output screen) for this specific case. I don't know if any other data will work for you.

    SELECT TMP.ClaimId
      , CASE WHEN TMP.RowNum = 1 THEN TMP.Name ELSE NULL END AS ClaimName
      , CASE WHEN TMP.RowNum = 1 THEN TMP.Note ELSE NULL END AS Note
      , TMP.PartId
      , TMP.PartNumber
      , TMP.PartCost
      , JOIN_L.LaborId
      , JOIN_L.LaborCost
    FROM (
      SELECT C.ClaimId, C.Name, C.Note, P.PartId, P.PartNumber, P.PartCost
      , ROW_NUMBER() OVER(PARTITION BY C.ClaimId ORDER BY P.PartId) AS RowNum
      FROM Claim AS C
      LEFT JOIN Part AS P ON C.ClaimId = P.ClaimId
    )AS TMP
    LEFT JOIN (
      SELECT *
      , ROW_NUMBER() OVER(PARTITION BY L.ClaimId ORDER BY L.ClaimId) AS RowNum
      FROM Labor AS L
    ) AS JOIN_L ON (TMP.ClaimId = JOIN_L.ClaimId AND TMP.RowNum = JOIN_L.RowNum)
    ORDER BY TMP.ClaimId

Upvotes: 1

huMpty duMpty
huMpty duMpty

Reputation: 14470

Not sure why you tried CTE here

   Select C.ClaimId,C.name,C.Note,P.PartId,P.PartNumber,P.PartCost,L.LabourId,L.LabourCost
   From Claim C
        Left Outer Join Part P On P.ClaimId = C.ClaimId
        Left Outer Join Labor L On L.ClaimId=C.ClaimId

Upvotes: 0

Related Questions