Path: text.usenetserver.com!out01a.usenetserver.com!news.usenetserver.com!in04.usenetserver.com!news.usenetserver.com!nx01.iad01.newshosting.com!newshosting.com!post01.iad01!not-for-mail
Date: Wed, 10 Oct 2007 06:01:15 -0700
From: DA Morgan <damorgan@psoug.org>
Organization: Puget Sound Oracle Users Group
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.misc
Subject: Re: Multiple datasets in one dataset
References: <1192000031.838733.84970@22g2000hsm.googlegroups.com>
In-Reply-To: <1192000031.838733.84970@22g2000hsm.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <1192021261.874276@bubbleator.drizzle.com>
Cache-Post-Path: bubbleator.drizzle.com!unknown@216.162.218.178
X-Cache: nntpcache 3.0.1 (see http://www.nntpcache.org/)
Lines: 81
X-Complaints-To: abuse@csolutions.net
Xref: usenetserver.com comp.databases.oracle.misc:250185
X-Received-Date: Wed, 10 Oct 2007 09:01:02 EDT (text.usenetserver.com)

roman.morokutti@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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org