Re: Multiple datasets in one dataset

roman.morokutti_at_googlemail.com wrote:
> Hi,
>
> I have the following problem. I have a table with the following
> schema:
>
> CREATE TABLE FOO
> ( "WP" VARCHAR2(10 BYTE),
> "VALUE_GROUP" NUMBER(4,0),
> "CURR_MS" NUMBER(38,0),
> "VALUE_1" NUMBER(13,3),
> "VALUE_2" NUMBER(13,3),
> "VALUE_3" NUMBER(13,3),
> "VALUE_4" NUMBER(13,3),
> "VALUE_5" NUMBER(13,3),
> "VALUE_6" NUMBER(13,3),
> "VALUE_7" NUMBER(13,3),
> "VALUE_8" NUMBER(13,3),
> "VALUE_9" NUMBER(13,3),
> "VALUE_10" NUMBER(13,3)
> );
>
> The PK is WP, VALUE_GROUP, CURR_MS.
>
> In this table there are several datasets which are grouped by CURR_MS.
> And sets with this unique ID should be selected to be shown in one
> line.
> This would be no problem if there would be always the same number of
> lines grouped by one uniquely id (CURR_MS). The following statement
> does this job:
>
> This one consists of 3 values:
>
> select a.*, b.*, c.* from foo a, foo b, foo c
> where a.wp = '418'
> and a.value_group = 11 and b.value_group = 21 and c.value_group =
> 22
> and a.curr_ms = 28144336
> and a.curr_ms = curr_ms
> and a.curr_ms = curr_ms
> and a.wp = b.wp
> and a.wp = c.wp
> ;
>
> But there are several groups which consists solely of 1 or 2 lines. Is
> it possible to get them also with the same statement? Your help would
> be greatly appreciated. Thanks in advance.
>
> Regards
>
> Roman

One look at the table design is enough to convince me that your problem is not writing a SQL statement rather it is understanding basic relational concepts.

Turn:

 > 	"VALUE_1" NUMBER(13,3),
 > 	"VALUE_2" NUMBER(13,3),
 > 	"VALUE_3" NUMBER(13,3),
 > 	"VALUE_4" NUMBER(13,3),
 > 	"VALUE_5" NUMBER(13,3),
 > 	"VALUE_6" NUMBER(13,3),
 > 	"VALUE_7" NUMBER(13,3),
 > 	"VALUE_8" NUMBER(13,3),
 > 	"VALUE_9" NUMBER(13,3),
 > 	"VALUE_10" NUMBER(13,3)

Into:

VAL_NUM 1 through 10
VALUE the value

Think vertically not horizontally.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org