tricky question

[kaz]

Hi all, I have a question, I am not sure if this can be answered in a simple
query or whether it requires pl/sql code, can someone assist?

Table structure looks like:
Student(s) Enrolment(e)
---------- ------------
stu_no stu_no
name unit_code
degree result

Now I need to make a report showing the average mark in each unit by degree
course, so the report should look something like this:

Unit degree M402 degree S366 degree S367
----- ------------ ----------- -----------
SCC103 54 63 72
SCC205 46 88 56
SCC312 54 72 61

- if anyone has a suggest on how to go about it I would be very appreciative.

Thanks, Karen

[Rohit Wason]

Kaz,

I see the problem here. Not that it can't be solved using one query, but
I have some doubts. Let me clear first what I get out of your question:

See this query:

select e.unit,
s.deg,
avg(e.result)
from Student s,
Enrolment e
where s.stu_no = e.stu_no
group by
unit_code,
degree

I assume that there is a one-to-many (or one-to-one) relationship between
"s" and "e". I hope I am right here.

So the above query is capable of giving me a matrix of:

Unit Degree Avg-Result
---- ------ ----------
U1 D1 44
U1 D2 56
U1 D3 78
U1 D4 24
U2 D1 56
U3 D1 89
U3 D2 44
U3 D3 57
U3 D4 22

(and so on)

Notice that the data is in a vertical form w.r.t the Degrees (D1, D2 etc.)
Whereas you want it to be horizontal. This is called transformation and I
am yet to find a "pure mathematical" derivation of Transformation, where
you w/o knowing the possible values of degree (in this case), can transform
the data vertically/horizontally.

Wherever I have queried this, I got the answer - "it is possible, but you
should know what are the possible key values you will use"

I had been wondering about this. I once got this answer from Kevin Loney
(author of Oracle8i: The Complete Reference) He also pointed the same point.
His book also explains the same point

*For a full example, see "Flipping a Table onto its side", p355 of Oracle8i:
The Complete Reference.*

But let me tell you in advance that if you don't know all the degree courses
in advance, this result can not be formed using single query (at least not
to my knowledge). If you do, its fine and you can transpose the above result
into:

Unit D1 D2 D3 D4
---- -- -- -- --
U1 44 56 78 24
...
...
and so on

HTH
Cheers
Rohit





"kaz" <karenyoung007@aol.com> wrote:
>
>Hi all, I have a question, I am not sure if this can be answered in a simple
>query or whether it requires pl/sql code, can someone assist?
>
>Table structure looks like:
>Student(s) Enrolment(e)
>---------- ------------
>stu_no stu_no
>name unit_code
>degree result
>
>Now I need to make a report showing the average mark in each unit by degree
>course, so the report should look something like this:
>
>Unit degree M402 degree S366 degree S367
>----- ------------ ----------- -----------
>SCC103 54 63 72
>SCC205 46 88 56
>SCC312 54 72 61
>
>- if anyone has a suggest on how to go about it I would be very appreciative.
>
>Thanks, Karen
>

[kaz]

Thanks for the assistance, I do know the degree codes in advance so I will
see how I go, thanks again

Kaz


"Rohit Wason" <rohitw@ggn.hcltech.com> wrote:
>
>Kaz,
>
>I see the problem here. Not that it can't be solved using one query, but
>I have some doubts. Let me clear first what I get out of your question:
>
>See this query:
>
>select e.unit,
> s.deg,
> avg(e.result)
>from Student s,
> Enrolment e
>where s.stu_no = e.stu_no
>group by
> unit_code,
> degree
>
>I assume that there is a one-to-many (or one-to-one) relationship between
>"s" and "e". I hope I am right here.
>
>So the above query is capable of giving me a matrix of:
>
>Unit Degree Avg-Result
>---- ------ ----------
>U1 D1 44
>U1 D2 56
>U1 D3 78
>U1 D4 24
>U2 D1 56
>U3 D1 89
>U3 D2 44
>U3 D3 57
>U3 D4 22
>
>(and so on)
>
>Notice that the data is in a vertical form w.r.t the Degrees (D1, D2 etc.)
>Whereas you want it to be horizontal. This is called transformation and
I
>am yet to find a "pure mathematical" derivation of Transformation, where
>you w/o knowing the possible values of degree (in this case), can transform
>the data vertically/horizontally.
>
>Wherever I have queried this, I got the answer - "it is possible, but you
>should know what are the possible key values you will use"
>
>I had been wondering about this. I once got this answer from Kevin Loney
>(author of Oracle8i: The Complete Reference) He also pointed the same point.
>His book also explains the same point
>
>*For a full example, see "Flipping a Table onto its side", p355 of Oracle8i:
>The Complete Reference.*
>
>But let me tell you in advance that if you don't know all the degree courses
>in advance, this result can not be formed using single query (at least not
>to my knowledge). If you do, its fine and you can transpose the above result
>into:
>
>Unit D1 D2 D3 D4
>---- -- -- -- --
>U1 44 56 78 24
>...
>...
>and so on
>
>HTH
>Cheers
>Rohit
>
>
>
>
>
>"kaz" <karenyoung007@aol.com> wrote:
>>
>>Hi all, I have a question, I am not sure if this can be answered in a simple
>>query or whether it requires pl/sql code, can someone assist?
>>
>>Table structure looks like:
>>Student(s) Enrolment(e)
>>---------- ------------
>>stu_no stu_no
>>name unit_code
>>degree result
>>
>>Now I need to make a report showing the average mark in each unit by degree
>>course, so the report should look something like this:
>>
>>Unit degree M402 degree S366 degree S367
>>----- ------------ ----------- -----------
>>SCC103 54 63 72
>>SCC205 46 88 56
>>SCC312 54 72 61
>>
>>- if anyone has a suggest on how to go about it I would be very appreciative.
>>
>>Thanks, Karen
>>
>

[psit]

Hi Kaz,
As Rohit's queries, I add some more for you.
select e.unit_code unit,
avg(decode(s.degree,M402',e.result,0) degree_M402,
avg(decode(s.degree,S366',e.result,0) degree_S366,
avg(decode(s.degree,S367',e.result,0) degree_S367,
.......... (the advance)...............
from Student s,
Enrolment e
where s.stu_no = e.stu_no
group by e.unit_code

psit

"kaz" <karenyoung007@aol.com> wrote:
>
>Thanks for the assistance, I do know the degree codes in advance so I will
>see how I go, thanks again
>
>Kaz
>
>
>"Rohit Wason" <rohitw@ggn.hcltech.com> wrote:
>>
>>Kaz,
>>
>>I see the problem here. Not that it can't be solved using one query, but
>>I have some doubts. Let me clear first what I get out of your question:
>>
>>See this query:
>>
>>select e.unit,
>> s.deg,
>> avg(e.result)
>>from Student s,
>> Enrolment e
>>where s.stu_no = e.stu_no
>>group by
>> unit_code,
>> degree
>>
>>I assume that there is a one-to-many (or one-to-one) relationship between
>>"s" and "e". I hope I am right here.
>>
>>So the above query is capable of giving me a matrix of:
>>
>>Unit Degree Avg-Result
>>---- ------ ----------
>>U1 D1 44
>>U1 D2 56
>>U1 D3 78
>>U1 D4 24
>>U2 D1 56
>>U3 D1 89
>>U3 D2 44
>>U3 D3 57
>>U3 D4 22
>>
>>(and so on)
>>
>>Notice that the data is in a vertical form w.r.t the Degrees (D1, D2 etc.)
>>Whereas you want it to be horizontal. This is called transformation and
>I
>>am yet to find a "pure mathematical" derivation of Transformation, where
>>you w/o knowing the possible values of degree (in this case), can transform
>>the data vertically/horizontally.
>>
>>Wherever I have queried this, I got the answer - "it is possible, but you
>>should know what are the possible key values you will use"
>>
>>I had been wondering about this. I once got this answer from Kevin Loney
>>(author of Oracle8i: The Complete Reference) He also pointed the same point.
>>His book also explains the same point
>>
>>*For a full example, see "Flipping a Table onto its side", p355 of Oracle8i:
>>The Complete Reference.*
>>
>>But let me tell you in advance that if you don't know all the degree courses
>>in advance, this result can not be formed using single query (at least
not
>>to my knowledge). If you do, its fine and you can transpose the above result
>>into:
>>
>>Unit D1 D2 D3 D4
>>---- -- -- -- --
>>U1 44 56 78 24
>>...
>>...
>>and so on
>>
>>HTH
>>Cheers
>>Rohit
>>
>>
>>
>>
>>
>>"kaz" <karenyoung007@aol.com> wrote:
>>>
>>>Hi all, I have a question, I am not sure if this can be answered in a
simple
>>>query or whether it requires pl/sql code, can someone assist?
>>>
>>>Table structure looks like:
>>>Student(s) Enrolment(e)
>>>---------- ------------
>>>stu_no stu_no
>>>name unit_code
>>>degree result
>>>
>>>Now I need to make a report showing the average mark in each unit by degree
>>>course, so the report should look something like this:
>>>
>>>Unit degree M402 degree S366 degree S367
>>>----- ------------ ----------- -----------
>>>SCC103 54 63 72
>>>SCC205 46 88 56
>>>SCC312 54 72 61
>>>
>>>- if anyone has a suggest on how to go about it I would be very appreciative.
>>>
>>>Thanks, Karen
>>>
>>
>

[kaz]

Thank you very much, ,psit, you have saved me a lot of trouble and grief after
trying to write pl/sql code for several hours to answer my problem, it seems
that you are spot on with the decode function. Thanks again
Karen



"psit" <psit@chula.com> wrote:
>
>Hi Kaz,
> As Rohit's queries, I add some more for you.
>select e.unit_code unit,
> avg(decode(s.degree,M402',e.result,0) degree_M402,
> avg(decode(s.degree,S366',e.result,0) degree_S366,
> avg(decode(s.degree,S367',e.result,0) degree_S367,
> .......... (the advance)...............
>from Student s,
> Enrolment e
>where s.stu_no = e.stu_no
>group by e.unit_code
>
>psit
>
>"kaz" <karenyoung007@aol.com> wrote:
>>
>>Thanks for the assistance, I do know the degree codes in advance so I will
>>see how I go, thanks again
>>
>>Kaz
>>
>>
>>"Rohit Wason" <rohitw@ggn.hcltech.com> wrote:
>>>
>>>Kaz,
>>>
>>>I see the problem here. Not that it can't be solved using one query, but
>>>I have some doubts. Let me clear first what I get out of your question:
>>>
>>>See this query:
>>>
>>>select e.unit,
>>> s.deg,
>>> avg(e.result)
>>>from Student s,
>>> Enrolment e
>>>where s.stu_no = e.stu_no
>>>group by
>>> unit_code,
>>> degree
>>>
>>>I assume that there is a one-to-many (or one-to-one) relationship between
>>>"s" and "e". I hope I am right here.
>>>
>>>So the above query is capable of giving me a matrix of:
>>>
>>>Unit Degree Avg-Result
>>>---- ------ ----------
>>>U1 D1 44
>>>U1 D2 56
>>>U1 D3 78
>>>U1 D4 24
>>>U2 D1 56
>>>U3 D1 89
>>>U3 D2 44
>>>U3 D3 57
>>>U3 D4 22
>>>
>>>(and so on)
>>>
>>>Notice that the data is in a vertical form w.r.t the Degrees (D1, D2 etc.)
>>>Whereas you want it to be horizontal. This is called transformation and
>>I
>>>am yet to find a "pure mathematical" derivation of Transformation, where
>>>you w/o knowing the possible values of degree (in this case), can transform
>>>the data vertically/horizontally.
>>>
>>>Wherever I have queried this, I got the answer - "it is possible, but
you
>>>should know what are the possible key values you will use"
>>>
>>>I had been wondering about this. I once got this answer from Kevin Loney
>>>(author of Oracle8i: The Complete Reference) He also pointed the same
point.
>>>His book also explains the same point
>>>
>>>*For a full example, see "Flipping a Table onto its side", p355 of Oracle8i:
>>>The Complete Reference.*
>>>
>>>But let me tell you in advance that if you don't know all the degree courses
>>>in advance, this result can not be formed using single query (at least
>not
>>>to my knowledge). If you do, its fine and you can transpose the above
result
>>>into:
>>>
>>>Unit D1 D2 D3 D4
>>>---- -- -- -- --
>>>U1 44 56 78 24
>>>...
>>>...
>>>and so on
>>>
>>>HTH
>>>Cheers
>>>Rohit
>>>
>>>
>>>
>>>
>>>
>>>"kaz" <karenyoung007@aol.com> wrote:
>>>>
>>>>Hi all, I have a question, I am not sure if this can be answered in a
>simple
>>>>query or whether it requires pl/sql code, can someone assist?
>>>>
>>>>Table structure looks like:
>>>>Student(s) Enrolment(e)
>>>>---------- ------------
>>>>stu_no stu_no
>>>>name unit_code
>>>>degree result
>>>>
>>>>Now I need to make a report showing the average mark in each unit by
degree
>>>>course, so the report should look something like this:
>>>>
>>>>Unit degree M402 degree S366 degree S367
>>>>----- ------------ ----------- -----------
>>>>SCC103 54 63 72
>>>>SCC205 46 88 56
>>>>SCC312 54 72 61
>>>>
>>>>- if anyone has a suggest on how to go about it I would be very appreciative.
>>>>
>>>>Thanks, Karen
>>>>
>>>
>>
>

[psit]

You're welcome, Karen, I am an old Oracle version user, it take me a lot of
time to get the power of them without PL/SQL. ANd the decode function is
one of the several poerful Oracle function.
psit

"kaz" <karenyoung007@aol.com> wrote:
>
>Thank you very much, ,psit, you have saved me a lot of trouble and grief
after
>trying to write pl/sql code for several hours to answer my problem, it seems
>that you are spot on with the decode function. Thanks again
> Karen
>
>
>
>"psit" <psit@chula.com> wrote:
>>
>>Hi Kaz,
>> As Rohit's queries, I add some more for you.
>>select e.unit_code unit,
>> avg(decode(s.degree,M402',e.result,0) degree_M402,
>> avg(decode(s.degree,S366',e.result,0) degree_S366,
>> avg(decode(s.degree,S367',e.result,0) degree_S367,
>> .......... (the advance)...............
>>from Student s,
>> Enrolment e
>>where s.stu_no = e.stu_no
>>group by e.unit_code
>>
>>psit
>>
>>"kaz" <karenyoung007@aol.com> wrote:
>>>
>>>Thanks for the assistance, I do know the degree codes in advance so I
will
>>>see how I go, thanks again
>>>
>>>Kaz
>>>
>>>
>>>"Rohit Wason" <rohitw@ggn.hcltech.com> wrote:
>>>>
>>>>Kaz,
>>>>
>>>>I see the problem here. Not that it can't be solved using one query,
but
>>>>I have some doubts. Let me clear first what I get out of your question:
>>>>
>>>>See this query:
>>>>
>>>>select e.unit,
>>>> s.deg,
>>>> avg(e.result)
>>>>from Student s,
>>>> Enrolment e
>>>>where s.stu_no = e.stu_no
>>>>group by
>>>> unit_code,
>>>> degree
>>>>
>>>>I assume that there is a one-to-many (or one-to-one) relationship between
>>>>"s" and "e". I hope I am right here.
>>>>
>>>>So the above query is capable of giving me a matrix of:
>>>>
>>>>Unit Degree Avg-Result
>>>>---- ------ ----------
>>>>U1 D1 44
>>>>U1 D2 56
>>>>U1 D3 78
>>>>U1 D4 24
>>>>U2 D1 56
>>>>U3 D1 89
>>>>U3 D2 44
>>>>U3 D3 57
>>>>U3 D4 22
>>>>
>>>>(and so on)
>>>>
>>>>Notice that the data is in a vertical form w.r.t the Degrees (D1, D2
etc.)
>>>>Whereas you want it to be horizontal. This is called transformation and
>>>I
>>>>am yet to find a "pure mathematical" derivation of Transformation, where
>>>>you w/o knowing the possible values of degree (in this case), can transform
>>>>the data vertically/horizontally.
>>>>
>>>>Wherever I have queried this, I got the answer - "it is possible, but
>you
>>>>should know what are the possible key values you will use"
>>>>
>>>>I had been wondering about this. I once got this answer from Kevin Loney
>>>>(author of Oracle8i: The Complete Reference) He also pointed the same
>point.
>>>>His book also explains the same point
>>>>
>>>>*For a full example, see "Flipping a Table onto its side", p355 of Oracle8i:
>>>>The Complete Reference.*
>>>>
>>>>But let me tell you in advance that if you don't know all the degree
courses
>>>>in advance, this result can not be formed using single query (at least
>>not
>>>>to my knowledge). If you do, its fine and you can transpose the above
>result
>>>>into:
>>>>
>>>>Unit D1 D2 D3 D4
>>>>---- -- -- -- --
>>>>U1 44 56 78 24
>>>>...
>>>>...
>>>>and so on
>>>>
>>>>HTH
>>>>Cheers
>>>>Rohit
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>"kaz" <karenyoung007@aol.com> wrote:
>>>>>
>>>>>Hi all, I have a question, I am not sure if this can be answered in
a
>>simple
>>>>>query or whether it requires pl/sql code, can someone assist?
>>>>>
>>>>>Table structure looks like:
>>>>>Student(s) Enrolment(e)
>>>>>---------- ------------
>>>>>stu_no stu_no
>>>>>name unit_code
>>>>>degree result
>>>>>
>>>>>Now I need to make a report showing the average mark in each unit by
>degree
>>>>>course, so the report should look something like this:
>>>>>
>>>>>Unit degree M402 degree S366 degree S367
>>>>>----- ------------ ----------- -----------
>>>>>SCC103 54 63 72
>>>>>SCC205 46 88 56
>>>>>SCC312 54 72 61
>>>>>
>>>>>- if anyone has a suggest on how to go about it I would be very appreciative.
>>>>>
>>>>>Thanks, Karen
>>>>>
>>>>
>>>
>>
>