how to turn rows into columns

[graziaR]

Hi,
I have a table, call it TEST, in which a records of a person's test dates and scores are kept.
For instance, a person might have taken twice a toefl test and one a math test. The table would look like this:
personId test score date
1111 toefl 234 02/04/2005
1111 toefl 444 03/07/2006
1111 math 333 05/06/2006

I woudl like to loop through the records of that person and to put them all on one line. That is, I woudl like the result to show something like this:
personId test_1 score_1 date_1 test_2 score_2 date_2 test_3 score_3 date_3 ....

I am using Oracle 9i.

Many thanks for any suggestion !

 

[SantaMufasa]

Grazia,

Here are some data values:

SQL> select * from test;

PERSONID TEST            SCORE DT
-------- ---------- ---------- ----------
    1111 toefl             234 02/04/2005
    1111 toefl             444 03/07/2006
    1111 math              333 05/06/2006
    2222 chem              234 02/04/2005
    2222 econ              333 05/06/2006

Here is a user-defined function:

create or replace function get_tests (id_in number) return varchar2 is
        hold_tests varchar2(100);
    begin
        for x in (select score, dt from test where personid = id_in) loop
            hold_tests := hold_tests||' '||x.score||' '||x.dt;
        end loop;
        return trim(hold_tests);
    end;
/

Function created.

Here is a SQL query that uses the above to produce your results:

col personid format 9999
col tests format a50
select distinct personid, get_tests(personid) tests
    from test
  order by personid;

PERSONID TESTS
-------- --------------------------------------------
    1111 234 02/04/2005 444 03/07/2006 333 05/06/2006
    2222 234 02/04/2005 333 05/06/2006

Let us know if this is what you wanted.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[graziaR]

Thank you very much, Musafa.
I am new to Oracle and your suggestion has opened a whole new realm of possibilities.

Question: how could I modify the function you sent me so that there is one distinct column for each of the concatenated parts ?
The function as it is returns two columns, personid and test, but I would like a distinct column for score and one for the date. Would that be possible ?

G.

 

[SantaMufasa]

To clarify, Grazia, do you mean you want:
[tt]
Test1 Test1 Test2 Test2 Test3 Test3
PersonID Score Date Score Date Score Date
-------- ----- ---------- ----- ---------- ----- ----------
1111 234 02/04/2005 444 03/07/2006 333 05/06/2006
2222 234 02/04/2005 333 05/06/2006
[/tt]

...and how many possible sets of tests are the maximum?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[graziaR]

I do not have a maximum, though.
Let's say it is at most 10.
I can always change that maximum, I assume.
Or at worst, I can always parse the results of the function you sent me in get_tests.

BTW, you gave me a great solution to this problem.

Grazia

 

[SantaMufasa]

Grazia,

There is a straitforward and efficient method to accomplish what you want...it involves using an Oracle PL/SQL user-defined "Package", which supports what is called "persistent data". This is a rather advanced principle, but not too difficult to understand. It would be perfect for your specification.

I would happy to show you a proof of concept, but I must leave my office for an appointment presently. If you want help with such a solution, then perhaps the best way to quickly resolve your need is to contact me via my signature, below.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cmmrfrds]

The SQL coders in our company are not allowed to create functions - I guess the DBA does not want to maintain them. So, we solve the issue with Oracle Analytical Functions. In this case the function DENSE_RANK() can create a window on the person_id and rank the data within the window. The "order by" the window function defines the granularity of the data for ranking.

for example.
Select person_id,
(case when rank = 1 then yourdate else null end) as col1a,
(case when rank = 1 then score else null end) as col1b,
(case when rank = 2 then yourdate else null end) as col2a,
(case when rank = 2 then score else null end) as col2b,
etc.....
From
(
Select *,
Dense_rank() over (partition by person_id order by test, yourdate, score) as rank
From yourtable
) aview

Just in case you want to do this without the function.

 

[taupirho]

Try this out:

select personid,
max( decode( "col", 0, score ) ) r1c1,
max( decode( "col", 0, test ) ) r1c2,
max( decode( "col", 1, score ) ) r2c1,
max( decode( "col", 1, test ) ) r2c2,
max( decode( "col", 2, score ) ) r3c1,
max( decode( "col", 2, test ) ) r3c2
from (
select personid,score, test, trunc((rn-0.1)/3) , mod(rn-1,3) "col"
from (
select personid,score, test, row_number() over (order by personid,dte) rn
from tom
)
)
group by personid
order by personid