One Query

Hi,

I saw a similar post by someone else but there was no replies.

I need to write a query that will return columns in the same result row. The trick is that these columns must be decoded, and I think that the decode statement must have a fallback if no items match. This will not work if I want to decode each column with a single value and display it in the same row.....

Like this:

// DATA IN TABLE
SQL> select * from test;

C1                 C2 C3
---------- ---------- ---------
REC1               50 01-JAN-07
REC2               50 01-JAN-07
REC1              100 01-MAY-07
REC2               10 01-JUN-07

SELECT TO_CHAR(c3,'MM/DD/YYYY') a, DECODE(c1, 'REC1','Record 1') b, SUM(C2), DECODE(c1,'REC2','Record 2') c, SUM(C2) FROM test
GROUP BY c3, c1;

A B SUM(C2) C SUM(C2)
---------- -------- ---------- -------- ----------

01/01/2007 Record 1         50                  50
01/01/2007                  50 Record 2         50
05/01/2007 Record 1        100                 100
06/01/2007                  10 Record 2         10

This is not even close to what I want. What I am looking for is a display like this:

A B SUM(C2) C SUM(C2)
---------- -------- ---------- -------- ----------

01/01/2007 Record1      50     Record2     50
05/01/2007 Record1     100
06/01/2007 Record2      10

The key here is that I want to put things in one row and group them and sum the proper columns.

I do not think this can be done without pl/sql. Received on Mon Sep 15 2008 - 08:17:26 CDT