CODEWITHSUNDEEP
sql
Bob
Bob

Reputation: 885

Extract data from multiple tables

I have 2 tables

TABLE A 

INV    AMT   DISC
1001  1500    150
1002  3000    300

TABLE B 

INV  DESC        AMT
1001 CHARGES     100
1001 FREIGHT      30
1001 INSURANCE    20
1002 CHARGES     215
1002 FREIGHT      32
1002 INSURANCE    25

For Table B, I used the SQL here to convert row to column format:

SELECT t.inv,
         MAX(CASE WHEN t.description = 'CHARGES' THEN t.amount ELSE NULL END) AS charges,
         MAX(CASE WHEN t.description = 'FREIGHT' THEN t.amount ELSE NULL END) AS freight,
         MAX(CASE WHEN t.description = 'INSURANCE' THEN t.amount ELSE NULL END) AS insurance
    FROM TABLE B
GROUP BY t.inv
ORDER BY t.inv

How can I combine the data into this format using SQL:

INV     AMT   DISC    CHARGES FREIGHT INSURANCE
1001   1500    150      100      30       20
1002   3000    300      215      32       25

Thanks.

Upvotes: 0

Views: 925

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

You need to join between Table A and you query against Table B

declare @TableA as table (inv int, amount int, disc int)
declare @TableB as table (inv int, description varchar(50), amount int)

insert into @TableA values (1001, 1500, 105)
insert into @TableA values (1002, 3000, 300)

insert into @TableB values (1001, 'CHARGES', 100)
insert into @TableB values (1001, 'FREIGHT', 30)
insert into @TableB values (1001, 'INSURANCE', 20)
insert into @TableB values (1002, 'CHARGES', 215)
insert into @TableB values (1002, 'FREIGHT', 32)
insert into @TableB values (1002, 'INSURANCE', 25)

select
    A.inv,
    A.amount,
    A.disc,
    B.charges,
    B.freight,
    B.insurance
from @TableA as A
    inner join (
                SELECT t.inv,
                       MAX(CASE WHEN t.description = 'CHARGES' THEN t.amount ELSE NULL END) AS charges,
                       MAX(CASE WHEN t.description = 'FREIGHT' THEN t.amount ELSE NULL END) AS freight,
                       MAX(CASE WHEN t.description = 'INSURANCE' THEN t.amount ELSE NULL END) AS insurance
                FROM @TableB as t
                GROUP BY t.inv) as B
        on A.inv = B.inv
where
    B.charges = 100 and
    A.inv = 1001

Output is

Output from query

Upvotes: 4

Related Questions