Pivot and/or crosstab? 1

[oradba101]

Good morning,

I have a table that records all electronic signatures. The structure looks like this:

trt_plan_id client_id page_name seq emp_id
----------- --------- --------- --- ------
121706 1111 test 1 9999
121706 1111 test 2 8888
121706 1111 test 3 7777
121706 1111 test 4 0
121706 1111 test 5 0
121706 1111 test_2 1 9999
121706 1111 test_2 2 8888
121706 1111 test_2 3 7777
121706 1111 test_2 4 0
121706 1111 test_2 5 0


The are five signature lines on each of the pages and the emp_id is the person who electronically signed each one.

What I need to do is this:

trt_plan_id client_id page_name 1 2 3 4 5
----------- --------- --------- ---- ---- ---- ---- ----
121706 1111 test 9999 8888 7777 0 0
121706 1111 test_2 9999 8888 7777 0 0

If possible, I would prefer to use a view to do this.

Regards,


William Chadbourne
Oracle DBA

 

[taupirho]

Try this SQL to get what you want


select
trt_plan_id,
page_name ,
client_id,
max(decode(seq,1,emp_id)) a,
max(decode(seq,2,emp_id)) b,
max(decode(seq,3,emp_id)) c,
max( decode(seq,4,emp_id)) d,
max (decode(seq,5,emp_id)) e
from tom
group by trt_plan_id,client_id,page_name

 

[oradba101]

Thanks,

If I knew how to award a star then I would.

Regards,


William Chadbourne
Oracle DBA

 

[KenCunningham]

William - bottom left of taupirho's post - 'Thank taupihiro for this valuable post'. Click and award.

I don't mind people who aren't what they seem. I just wish they'd make their mind up.

 

[oradba101]

Hi, Ken

A lot of times I see boxes titled CODE. How do I do this?

Regards,


William Chadbourne
Oracle DBA

 

[karluk]

Put your code here.

 

[karluk]

So, if I cut and paste my previous post and make sure that the "Process TGML" option is selected, I will get "Put your code here." embedded in a code block.

Put your code here.

 

[oradba101]

Thanks, Karluk

Regards,


William Chadbourne
Oracle DBA