join 2 tables (transform/pivot) 2

[1974maiden]

Hello,

I trying to join 2 tables, I know in some programs SQL the Transform/Pivot works but I'm no too sure how to do it in DB2, any help is appreciated.

Table1
LID Lot Date
1 100 01012004

Table2
PID LID Job Value
1 1 JobA 1.5
2 1 JobB 3.5
3 1 JobC 5.0
4 1 JobD 5.5
5 1 JobE 2.0

I'm looking to produce the following output ....

LID Lot Date JobA JobB JobC JobD JobE
1 100 01012004 1.5 3.5 5.0 5.5 2.0

Thankyou,
Jimmy

 

[pkc74]

This is not a very flexible solution but will do the trick. This works when the domain of the column Job is a set of known values at the time of writing the query.

SELECT
lid,
lot,
date,
max(joba) as joba,
max(jobb) as jobb,
max(jobc) as jobc ,
max(jobd) as jobd,
max(jobe) as jobe
FROM
(
SELECT
t1.lid as lid,
t1.lot as lot,
t1.date as date,
CASE job
WHEN 'JobA'
THEN value
ELSE null
END
as joba,
CASE job
WHEN 'JobB'
THEN value
ELSE null
END
as jobb,
CASE job
WHEN 'JobC'
THEN value
ELSE null
END
as jobc,
CASE job
WHEN 'JobD'
THEN value
ELSE null
END
as jobd ,
CASE job
WHEN 'JobE'
THEN value
ELSE null
END
as jobe
FROM table1 t1,
table2 t2
WHERE t1.lid = t2.lid
)
as temp_tab;

N.B. You could apply the max function on the base query itself ,but chose to write it this way for better clarity.

Trust this helps.

Praveen

 

[BrianTyler]

This is a variation of the first reply. The one you use would depend on how you wish to deal with multiple occurrencies.

SELECT
t1.lid as lid,
t1.lot as lot,
t1.date as date,
sum(CASE job
WHEN 'JobA'
THEN value
ELSE 0
END)
as joba,
SUM(CASE job
WHEN 'JobB'
THEN value
ELSE 0
END)
as jobb,
SUM(CASE job
WHEN 'JobC'
THEN value
ELSE 0
END)
as jobc,
SUM(CASE job
WHEN 'JobD'
THEN value
ELSE 0)
END
as jobd ,
SUM(CASE job
WHEN 'JobE'
THEN value
ELSE 0
END)
as jobe
FROM table1 t1,
table2 t2
WHERE t1.lid = t2.lid