Q: view with parameters? How do I convert rows to columns?

From: devnull <devnull_at_open.ch>
Date: Mon, 8 Mar 1999 19:11:44 +0100
Message-ID: <7c13q7$ljl$1@news.eunet.ch>

Dear readers and co-coders,

I need to convert rows of data into columns.
(this looks long, but its just the data)

Take the following table

CREATE TABLE MILESTONES MLSTN_ID NUMBER(10) NOT NULL, PROJ_PROJ_ID NUMBER(10) NOT NULL, NAME VARCHAR2(100) NOT NULL ) ;

select MLSTN_ID, PROJ_PROJ_ID, NAME from milestones

21 21 Revise layout
1 101 MS BP
41 62 Milestones
2 101 MS2
3 101 MS3
4 101 MS4
5 101 MS5
6 101 MS6
7 101 MS7
8 101 MS8
9 101 MS9
10 101 MS10
11 101 MS11

Assume that I have for each project a number of milestones, and I would like to display 3 of them in a table:

Milestones for Project 101

1                    2           3
MS BP          MS2       MS3

Now this seems a fairly common problem, but I have trouble getting it right. Usually, I would use some decodes and group functions, but here we have strings, which makes things harder, and we have no reliable distinction between the single entries of milestones

I try to use rownum:

select rownum ind, mlstn_id mid, proj_proj_id pid from milestones IND MID PID

1 1 101
2 21 21
3 41 62
4 2 101
5 3 101
6 4 101
7 5 101
8 6 101
9 7 101
10 8 101
11 9 101
12 10 101
13 11 101

and then decode:

select
pid,

    get_ms_name (sum (decode (ind, 1, mid, 0))) n1,
    get_ms_name (sum (decode (ind, 2, mid, 0))) n2,
    get_ms_name (sum (decode (ind, 3, mid, 0))) n3

from ( select rownum ind, mlstn_id mid, proj_proj_id pid from milestones ) group by pid

where get_ms_name is a function that opens up a cursor to deliver the text which belongs to a given Milstone-ID (could also use list as a group operator and then try to get rid of all the ',' somehow...) I will get the following:

PID    N1                  N2                        N3
===========================================
101 MS BP
62                                                           Milestones
21                              Revise layout

SOmehow I need to get a where clause into the whole construct, which restricts the returned rows to one project, but how?

If I use :
select * from (
select
pid,

    get_ms_name (sum (decode (ind, 1, mid, 0))) n1,
    get_ms_name (sum (decode (ind, 2, mid, 0))) n2,
    get_ms_name (sum (decode (ind, 3, mid, 0))) n3

from ( select rownum ind, mlstn_id mid, proj_proj_id pid from milestones ) group by pid
) where pid = 101

all I get is:

PID N1                  N2               N3
=============================
101 MS BP          NULL          NULL

i.e. I will not find the other milestone entries for project 101 because the innermost select returns all rows before considering the where-statement. I see no way to provide the innermost select with the where-clause (I would need something like a view with parameters)

Somehow there must be another way. Do you know one?

Thanks for your time,

Boris Kraft
Open Systems AG
(please reply to kraft at open.ch)
Received on Mon Mar 08 1999 - 12:11:44 CST