Joining same table multiple times in SQL

Question

I am having a table which will have 3 or more rows for each tran_ref column and each row will have amount as well as tran_id follwing is my input table

tran_ref | tran_id | amount
T1       |01.      | 9
T1       |02.      | -8
T1       |03       | -1
T2       |01.      | 5
T2       |02.      | -4
T2       |03       | -1
T2       |03       | -1

I want one row for each tran_ref in some other table as

tran_ref |  amount_01 | amount_02| amount_03 | count_of_ref_rows
T1       | 9        | -8     | -1.     | 3
T2       | 5        | -4     | -2.     | 4

I tried with join but didn't worked as expected Thanks :)

Updated data

If this is the table

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

mysql and ms sql server are two different database products with different implementations of sql. Removed the conflicting product tags. Pls add the one back that you actually use. Also, this operation is called pivoting. Whilst it is possible to perform such transformation in sql, it is usually more efficient to do this from application code. For both mysql and ms sql server several questions on pivoting have already been answered, so this question is a duplicate. — Shadow, Sep 06 '20 at 12:11
Boht the below queries will work perfectly fine but I have assumed that tran_id is an integer. — Atif, Sep 06 '20 at 12:47

Atif · Accepted Answer · 2020-09-06T12:45:15.470

1

You can Case expression:

SELECT tran_ref, SUM(CASE WHEN tran_id = 1 THEN AMOUNT END) AS AMOUNT_01,
SUM(CASE WHEN tran_id = 2 THEN AMOUNT END) AS AMOUNT_02,
SUM(CASE WHEN tran_id = 3 THEN AMOUNT END) AS AMOUNT_03,
COUNT(*) count_of_ref_rows
FROM TABLE1
GROUP BY tran_ref;

Also, you can use PIVOT clause for this:

select a.*, b.count_total from
(SELECT tran_ref, [1] as amount_01, [2] as amount_02, [3] as amount_03
FROM TABLE1
PIVOT(sum(amount) FOR tran_id IN ([1] , [2], [3])) pivot_table) a
 inner join (SELECT tran_ref, count(1) count_total
FROM TABLE1 group by tran_ref) b on (a.tran_ref = b.tran_ref);

edited Sep 06 '20 at 12:45

answered Sep 06 '20 at 12:18

Atif

2,011
9
23

I tried using this case expression. It worked for me. But I have few more columns as currency and date but I have to take it from table only from row with tran_id as 01. How can we modify above case query ? – Sonali Sep 06 '20 at 14:32
Can you share updated data and expected result – Atif Sep 06 '20 at 14:43
I have updated the question – Sonali Sep 06 '20 at 16:41
This question is closed, so I can really answer on the same question but case will still be able to help you. Request you to post a new question with this updated information. – Atif Sep 06 '20 at 16:44
OK.. Will post new quesyion thanks @Atif – Sonali Sep 06 '20 at 16:48
I have posted question at following link https://stackoverflow.com/questions/63767899/mssql-joining-multiple-rows-into-single?noredirect=1#comment112762725_63767899 – Sonali Sep 06 '20 at 19:14

Joining same table multiple times in SQL

1 Answers1

Linked

Related