join tables and return multiple rows with same main id as single rows

Question

Hi to al

I have two tables need to join and then the multiple row result with the same crb_pi_id from table2, will combine and return as single rows. it is possible? I used that result on printable report.

Thanks in advance.

table1 :

crb_pi_id,name,tel_no
1,john,1111111
2,paul,2222222

table2 :

crb_pd_id,crb_pi_id,account_name,amount
1,1,salary,500
2,1,utilities,800
3,2,transportation,300

result should

name,salary,utilities,trasportation

john,500,800,0
paul,0,0,300

Answer 1

select a.name, a.tel_no, sum(b.amount) as totamount

from table1 a left outer join table2 b on a.crb_pi_id = b.crb_pi_id
group by a.name, a.tel_no

You'll have to include whatever fields from table1 that you list in the select in the group statement

Answer 2

not sure you can really do that w/ straight sql aside from using a cursor and a temp variable being assigned the values of each cell value successively. or if you can use a higher level language you can use php's combination of implode() and string concat on the array from your query call.

edit 1: oh never mind... the question changed right after i posted my answer haha

edit 2: perhaps you wanted something like this? (untested pseudo)

select
    t1.name,
    case when (t2.crb_pi = t1.crb_pi) and (t2.account_name = 'salary') then t2.amount else 0 end as 'salary'
    case when (t2.crb_pi = t1.crb_pi) and (t2.account_name = 'utilities') then t2.amount else 0 end as 'utilities'
    case when (t2.crb_pi = t1.crb_pi) and (t2.account_name = 'transportation') then t2.amount else 0 end as 'transportation'    
from table1 t1
left join table2 t2
    on t2.crb_pi = t1.crb_pi

Answer 3

You can use with clause here and then group the results accordingly

select t1.name 'name', t2.amount 'salary',
CASE WHEN t2.crb_pi_id=t1.crb_pi_id and t2.crb_pd_id=2 then t2.amount ELSE 0 END 'utilities',
CASE WHEN t2.crb_pi_id=t1.crb_pi_id and t2.crb_pd_id=3 then t2.amount ELSE 0 END 'trans'
into #t3
from #t1 t1 inner join #t2 t2 on t1.crb_pi_id=t2.crb_pi_id

select name, max(salary), max(utilities), max(trans)
from t3
group by name