sql - Avoid repeated values in a join

Question

I have two tables - a header and a matrix/details.

*Header Table*               *Matrix / Details Table*
+----+--------+-----+        +----+--------+------+
| ID | Parent | Qty |        | ID | Child  | Qty  |
+----+--------+-----+        +----+--------+------+
| 1  |   A    | 10  |        | 1  |   X    | 100  |
| 2  |   B    | 20  |        | 1  |   Y    | 1000 |
| 3  |   C    | 30  |        | 2  |   X    | 200  |
+----+--------+-----+        | 2  |   Y    | 2000 |
                             | 3  |   X    | 30   |
                             | 3  |   Y    | 300  |
                             | 3  |   Z    | 3000 |
                             +----+--------+------+

I'm Joining these two tables based on ID.

I don't want the result to have duplicated values from header table. I expect a result like following:

*Current Result*                          *Expected Result*
+----+--------+-----+-------+------+      +----+--------+-----+-------+------+
| ID | Parent | Qty | Child | Qty  |      | ID | Parent | Qty | Child | Qty  |
+----+--------+-----+-------+------+      +----+--------+-----+-------+------+
| 1  |   A    | 10  |   X   | 100  |      | 1  |   A    | 10  |   X   | 100  |
| 1  |   A    | 10  |   Y   | 1000 |      |    |        |     |   Y   | 1000 |
| 2  |   B    | 20  |   X   | 200  |      | 2  |   B    | 20  |   X   | 200  |
| 2  |   B    | 20  |   Y   | 2000 |      |    |        |     |   Y   | 2000 |
| 3  |   C    | 30  |   X   | 30   |      | 3  |   C    | 30  |   X   | 30   |
| 3  |   C    | 30  |   Y   | 300  |      |    |        |     |   Y   | 300  |
| 3  |   C    | 30  |   Z   | 3000 |      |    |        |     |   Z   | 3000 |
+----+--------+-----+-------+------+      +----+--------+-----+-------+------+

Is this possible? If not any, alternate solution available?

Thanks in advance...

Answer 1

If you are using SQL Server,Try with the below query.

;WITH CTE_1
AS
(SELECT *,ROW_NUMBER()OVER(PARTITION BY ID,Parent,Quantity ORDER BY ID ) RNO
FROM Header  H
JOIN [Matrix / Details] M
 ON H.ID=M.ID)

 SELECT CASE WHEN RNO=1 THEN CAST(ID as VARCHAR(50)) ELSE '' END  ID,
        CASE WHEN RNO=1 THEN Parent ELSE '' END  Parent,
        CASE WHEN RNO=1 THEN cast(Quantity as VARCHAR(50)) ELSE '' END  Quantity,
        Child,Qty
 FROM  CTE_1
 ORDER BY ID,Parent,Quantity