Joining multiple rows into single

Question

I have a table which will have 3 or more rows for each tran_ref column value, and each row will have amount as well as tran_id.

The following is my input table - I want one row for each tran_ref in some other table as

tran_ref | tran_id | amount | Currency | Date
---------+---------+--------+----------+-----------
T1       |  01     |    9.  | USD      | 02-09-2020
T1       |  02     |   -8.  | INR      | 03-09-2020
T1       |  03     |   -1.  | INR      | 03-09-2020

Updated result needed

tran_ref | amount_01 | amount_02 | amount_03 | count | cur1 | cur23 | date1
---------+-----------+-----------+-----------+-------+------+-------+------
   T1    |      9    |     -8    |    -1     |   3   | USD  | INR   | 02-092020

Please note currency1 should be from tran_id = 1, currency23 from tranId23 and date should be only from tran_id 1

This is a classic pivot issue. Leanr to use PIVOT or a series of CTE's to help you flip the data into this format — Doug Coats, Sep 06 '20 at 19:08
Please **do not post the same [question](https://stackoverflow.com/questions/63763988/joining-same-table-multiple-times-in-sql) twice**. Your first question was a duplicate and this one too, and both doesn't include any attempt to solve your issue. — Ilyes, Sep 06 '20 at 20:47

Gordon Linoff · Answer 1 · 2020-09-07T15:09:53.940

3

Just use conditional aggregation:

SELECT tran_ref, 
       MAX(CASE WHEN tran_id = 1 THEN AMOUNT END) AS AMOUNT_01,
       MAX(CASE WHEN tran_id = 2 THEN AMOUNT END) AS AMOUNT_02,
       MAX(CASE WHEN tran_id = 3 THEN AMOUNT END) AS AMOUNT_03,
       COUNT(*) as count,
       MAX(CASE WHEN tran_id = 1 THEN currency END) AS currency_01,
       MAX(CASE WHEN tran_id = 2 THEN currency END) AS currency_02,
       MAX(CASE WHEN tran_id = 3 THEN currency END) AS currency_03,
       MIN(date)
FROM t
GROUP BY tran_ref;

edited Sep 07 '20 at 15:09

answered Sep 06 '20 at 19:54

Gordon Linoff

1,242,037
58
646
786

Date is missing – Atif Sep 07 '20 at 08:59

score 2 · Answer 2 · edited Sep 06 '20 at 19:50

You can use the below query with CTE and case to get the desired result:

WIT HCTE1 AS
(
    SELECT 
        tran_ref, 
        MIN (CASE WHEN tran_id = 1 THEN [currency] END) AS cur1, 
        MIN (CASE WHEN tran_id <> 1 THEN [currency] END) AS cur23, 
        MIN (CASE WHEN tran_id = 1 THEN [Date] END) AS DATE_UPDATE
    FROM 
        Table1_pivot1 
    GROUP BY
        tran_ref
)
SELECT 
    A.tran_ref, 
    SUM(CASE WHEN A.tran_id = 1 THEN AMOUNT END) AS AMOUNT_01,
    SUM(CASE WHEN A.tran_id = 2 THEN AMOUNT END) AS AMOUNT_02,
    SUM(CASE WHEN A.tran_id = 3 THEN AMOUNT END) AS AMOUNT_03,
    MIN(B.CUR1) AS CUR1,
    MIN(B.CUR23) AS CUR23,
    MIN(DATE_UPDATE) AS DATE_UPDATE,
    COUNT(*) count_of_ref_rows
FROM 
    Table1_pivot1 A 
INNER JOIN 
    CTE1 B ON (A.TRAN_REF = B.TRAN_REF)
GROUP BY 
    A.tran_ref;

Joining multiple rows into single

2 Answers2

Linked