Re: QUERY QUESTION

From: agis <stag_at_hq.acn.gr>
Date: Fri, 22 Oct 2004 13:45:06 +0300
Message-ID: <claobi$1isi$1@ulysses.noc.ntua.gr>

"Sybrand Bakker" <sybrandb_at_hccnet.nl> wrote in message news:7rnhn0hbifabesurmvktmlotu5bf7iorp8_at_4ax.com...
> On Fri, 22 Oct 2004 11:55:12 +0300, "agis" <stag_at_hq.acn.gr> wrote:
>
>>Hi to all,
>>
>>I have a table like the below :
>>
>>val1 val2 val3
>>--------------
>>81 1 121564
>>81 2 5616516
>>81 3 15/07/03
>>81 4
>>81 5 checked
>>81 6
>>81 9 checked
>>81 10
>>81 11 checked
>>81 12
>>81 13 checked
>>81 14
>>81 15 checked
>>81 17
>>81 18 checked
>>81 19
>>81 20
>>81 23 12
>>81 24
>>82 1 1654
>>82 2 4564
>>82 3 16/07/03
>>82 4 checked
>>82 5 checked
>>82 6 checked
>>82 9
>>82 10
>>
>>and i want a query to have the following result set :
>>
>>81 121564 5616516 15/07/03....
>>82 1654 4564 .....
>>.....
>>
>>I dont need to saw val2
>>
>>Can anyone help with this ?
>>
>>Thanks
>>
>>
>>
>>
>
> You need a PL/SQL procedure for this purpose, you can't resolve it
> with ordinary SQL. Examples of this kind of procedure have already
> been posted several times!
> What, if anything, did you accomplish prior to posting this question?
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

I find out a way with normal SQL !!!!

select val1

,max(decode(myseq,1,val3))
,max(decode(myseq,2,val3))
,max(decode(myseq,3,val3))
,max(decode(myseq,4,val3))
,max(decode(myseq,5,val3))
,max(decode(myseq,6,val3))
,max(decode(myseq,7,val3))
,max(decode(myseq,8,val3))
,max(decode(myseq,9,val3))

from (
  select val1,val3,
  row_number() over (partition by val1 order by val2) myseq   from (

     select a.inst_id val1 ,b.question_id val2     ,decode(rnumber,null,decode(rdate,null,rstring,rdate)) val3     from tacn_quest_instances a,tacn_quest_results b     where a.inst_id = b.inst_id and a.quest_id = 3     order by a.inst_id,b.question_id
    )
  )
group by val1
order by 1

This WORKS FINE but u must know the maximum myseq in order not to loose displaying a column

I am working it because i held into an ora 01467 Received on Fri Oct 22 2004 - 05:45:06 CDT