| How do I combine multiple rows into one row like this..? [message #372936] |
Mon, 19 March 2001 11:12  |
buchie
Messages: 5
Registered: March 2001
|
Junior Member |
|
|
I want to change table T1 like table T2 using SQL.
Table T1,
a b c <= field name
-----------------------
1 1 10
1 2 20
1 3 30
2 1 14
2 3 18
3 1 21
.......
I want to reassemble table T1 like table T2.
How can I do that?
How can I do that using 'select...'?
Thanks.
Table t2
w x y x
-----------------------
1 10 20 30
2 14 0 18
3 21
.............
|
|
|
|
| Re: How do I combine multiple rows into one row like this..? [message #372945 is a reply to message #372936] |
Mon, 19 March 2001 16:42  |
Joachim Lindner
Messages: 30
Registered: February 2001
|
Member |
|
|
Hi buche,
the following is based on the assumption that the number of occurences
of a specific value of t1.a is limited by the "width" of table t2, e.g.
the number of columns t2 consists of, or vice versa. If this is not the
case, there is a general error in your reasoning on the underlying problem
and its solution, since there is no mapping from t1 to t2, then. Right ?
Here's one static-SQL approach that works and is easy to understand. It
requires creating a user-defined function and thus would not be considered
a "pure" SQL solution by some of us. Be aware, that I haven't tested it.
---------------------------------------------------------------------------
create or replace function cval (aval number, bval number)
return number is
val number;
begin
select c into val
from t1
where a = aval and b = bval;
return val;
exception
when no_data_found then
return 0;
end;
/
insert into t2 (w, x, y, z)
select a, cval(a,1), cval(a,2), cval(a,3)
from t1;
---------------------------------------------------------------------------
I don't want to present my second solution here, because it is rather "ugly".
It is a pure SQL solution but complicated since it is based on a complex inline
view (it is complex since it must return zero values for non-existing c-values)
which is self-joined three times. Again, non-generic.
A third, promising and maybe more elegant approach is to use CURSOR Expressions.
However, I have to admit that I have never applied this concept, so far.
Cheers, Joachim
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
