Transform Row to Columns

IDEMP IDDEPTO
1 A
1 B
1 E
2 A
2 C

Transform to:

IDEMP FIRSTDEPTO SECONDDEPTO THIRDDEPTO
1 A B E
2 A C

HOW??

See pivot in the documentation:

http://publib.boulder.ibm.com/infoce...n/c0006901.htm

crisitano.lopes-at-accenture.com wrote:

> IDEMP IDDEPTO
> 1 A
> 1 B
> 1 E
> 2 A
> 2 C
>
> Transform to:
>
> IDEMP FIRSTDEPTO SECONDDEPTO THIRDDEPTO
> 1 A B E
> 2 A C
>
> HOW??

crisitano.lopes-at-accenture.com wrote:

> IDEMP IDDEPTO
> 1 A
> 1 B
> 1 E
> 2 A
> 2 C
>
> Transform to:
>
> IDEMP FIRSTDEPTO SECONDDEPTO THIRDDEPTO
> 1 A B E
> 2 A C
>
> HOW??
Hi,

First of, I construct a test table for your data:

$ db2 "select * from vert"

IDEMP IDDEPTO
----------- -------
1 A
1 B
1 E
2 A
2 C

5 record(s) selected.

Then I generate ordinal values for the depts using OLAP functions:

$ db2 "select v.*, rank() over (partition by idemp order by iddepto)
from vert v"

IDEMP IDDEPTO 3
----------- ------- --------------------
1 A 1
1 B 2
1 E 3
2 A 1
2 C 2

5 record(s) selected.

Now, using these OLAP functions, we can transform to horizontal form :

with ordered_vert
(iddept, iddepto, ordval) as (
select v.*, rank() over (partition by idemp order by
iddepto) from vert v
)
select main.iddept, first.iddepto as fcol, second.iddepto as scol,
third.iddepto as tcol
from (
select iddept
from ordered_vert group by iddept) as main
left outer join

(
select iddept, iddepto
from ordered_vert
where ordval = 1
group by iddept, iddepto) as first
on main.iddept = first.iddept

left outer join

(

select iddept, iddepto
from ordered_vert
where ordval = 2
group by iddept, iddepto) as second
on main.iddept = second.iddept

left outer join

(
select iddept, iddepto
from ordered_vert
where ordval = 3
group by iddept, iddepto) as third
on main.iddept = third.iddept;

This produces:

$ db2 -tvf horiz.sql
with ordered_vert (iddept, iddepto, ordval) as ( select v.*, rank() over
(partition by idemp order by iddepto) from vert v ) select main.iddept,
first.iddepto as fcol, second.iddepto as scol, third.iddepto as tcol
from ( select iddept from ordered_vert group by iddept) as main left
outer join ( select iddept, iddepto from ordered_vert where ordval = 1
group by iddept, iddepto) as first on main.iddept = first.iddept left
outer join ( select iddept, iddepto from ordered_vert where ordval = 2
group by iddept, iddepto) as second on main.iddept = second.iddept left
outer join ( select iddept, iddepto from ordered_vert where ordval = 3
group by iddept, iddepto) as third on main.iddept = third.iddept

IDDEPT FCOL SCOL TCOL
----------- ---- ---- ----
1 A B E
2 A C -

2 record(s) selected.


Regards,
Kenneth