Re: Same old question about row consolidation

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 25 Mar 2006 10:54:02 +0100
Message-ID: <44251339$0$21543$626a54ce@news.free.fr>

"JC" <joseph.chewning_at_targacept.com> a écrit dans le message de news: 1143263626.496315.146520_at_z34g2000cwc.googlegroups.com...
|I need help with a SQL statement or function that will consolidate two
| or more rows. I need to join the row on lot_id and conc. The conc
| values are always 0,10 or 100. The following is how the rows are
| configured.
|
| lot_id conc field1 field2 field3
| 1 0 1.0 null null
| 1 10 .01 nul null
| 1 100 .02 null null
| 2 100 null .09 null
| 3 10 null null 1
| 3 0 null null .003
|
| the way these guys want to see the data is for each column return
| something like
|
| lot_id field1 field2 field3
| 1 [0]1.0 [10].01[100].02
| 2 [100].09
| 3 [0].003 [10]1
|
| if someone could give me an example of how to do this it would be much
| appreciated!!!
|

Same old answer :)

SQL> SELECT * FROM tab ORDER BY lot_id, conc;

    LOT_ID CONC FIELD1 FIELD2 FIELD3 ---------- ---------- ---------- ---------- ----------

         1          0          1
         1         10        .01
         1        100        .02
         2        100                   .09
         3          0                             .003
         3         10                                1

6 rows selected.

SQL> COLUMN field1 FORMAT A25
SQL> COLUMN field2 FORMAT A10
SQL> COLUMN field3 FORMAT A15
SQL> WITH
  2    data AS (
  3      SELECT lot_id,
  4             DECODE(field1, NULL, '', '['||conc||']'||field1) field1,
  5             DECODE(field2, NULL, '', '['||conc||']'||field2) field2,
  6             DECODE(field3, NULL, '', '['||conc||']'||field3) field3,
  7             ROW_NUMBER() OVER (PARTITION BY lot_id ORDER BY conc) curr,
  8             ROW_NUMBER() OVER (PARTITION BY lot_id ORDER BY conc)-1 prev
  9      FROM tab

 10 )
 11 SELECT lot_id,

 12         SUBSTR(MAX(SYS_CONNECT_BY_PATH(field1,' ')),2) field1,
 13         SUBSTR(MAX(SYS_CONNECT_BY_PATH(field2,' ')),2) field2,
 14         SUBSTR(MAX(SYS_CONNECT_BY_PATH(field3,' ')),2) field3

 15 FROM data
 16 CONNECT BY PRIOR curr = prev AND PRIOR lot_id = lot_id  17 START WITH curr = 1
 18 GROUP BY lot_id
 19 ORDER BY lot_id
 20 /

    LOT_ID FIELD1                    FIELD2     FIELD3
---------- ------------------------- ---------- ---------------
         1 [0]1 [10].01 [100].02
         2                           [100].09
         3                                      [0].003 [10]1

3 rows selected.

Regards
Michel Cadot Received on Sat Mar 25 2006 - 03:54:02 CST