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

From: Virag Saksena <_at_sg.oracle.com>
Date: Mon, 15 Mar 1999 22:37:20 +0800
Message-ID: <36ED1B20.23D@sg.oracle.com>

Boris,

        It is not elegant but it works...

col name form A25
set recsep off

select m1.mlstn_id||chr(10)||m1.name name,
       m2.mlstn_id||chr(10)||m2.name name,
       m3.mlstn_id||chr(10)||m3.name name

from milestones m1, milestones m2, milestones m3

where m1.proj_proj_id = 101
  and m2.mlstn_id(+) > m1.mlstn_id
  and m3.mlstn_id(+) > m2.mlstn_id
  and not exists ( select null from milestones mc
           where mc.proj_proj_id = m1.proj_proj_id
             and mc.mlstn_id < m1.mlstn_id
             group by mc.proj_proj_id
            having mod(count(*),3) <> 0 )

  and m2.proj_proj_id(+) = m1.proj_proj_id   and not exists ( select null from milestones mc

           where mc.proj_proj_id = m1.proj_proj_id
             and mc.mlstn_id < m2.mlstn_id
             group by mc.proj_proj_id
            having mod(count(*),3) <> 1 )
  and not exists ( select NULL from milestones mi
      where mi.proj_proj_id = m1.proj_proj_id
        and mi.mlstn_id > m1.mlstn_id
        and mi.mlstn_id < m2.mlstn_id
        and not exists ( select null from milestones mc
           where mc.proj_proj_id = m1.proj_proj_id
             and mc.mlstn_id < mi.mlstn_id
             and mc.mlstn_id < mi.mlstn_id
             group by mc.proj_proj_id
            having mod(count(*),3) <> 1 ))

  and m3.proj_proj_id(+) = m2.proj_proj_id   and not exists ( select null from milestones mc

           where mc.proj_proj_id = m1.proj_proj_id
             and mc.mlstn_id < m3.mlstn_id
             group by mc.proj_proj_id
            having mod(count(*),3) <> 2 )
  and not exists ( select NULL from milestones mi
      where mi.proj_proj_id = m1.proj_proj_id
        and mi.mlstn_id > m2.mlstn_id
        and mi.mlstn_id < m3.mlstn_id
        and not exists ( select null from milestones mc
           where mc.proj_proj_id = m1.proj_proj_id
             and mc.mlstn_id < mi.mlstn_id
             and mc.mlstn_id < mi.mlstn_id
             group by mc.proj_proj_id
            having mod(count(*),3) <> 2 ));

NAME                      NAME                      NAME
------------------------- -------------------------
-------------------------
1                         2                         3
MS BP                     MS2                       MS3
4                         5                         6
MS4                       MS5                       MS6
7                         8                         9
MS7                       MS8                       MS9
10                        11
MS10                      MS11

Regards,  

Virag  

---

Consulting Manager 6 Temasek Boulevard mobile: +65 9830 0889
Mission Critical Systems Suntec Tower Four, #12-01 direct: +65 431 1628
South Asia Region Singapore 038986 fax: +65 333 8806

---

devnull wrote:
>
> 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
> (kraft at open.ch)

-- Received on Mon Mar 15 1999 - 08:37:20 CST