Oracle SQL get values on the same row using CTE

Question

I have a small table balance:

TYPE    BNO    AMT
DEB-AA  1111   50
CRED-AA 2222   -50
CRED-AA 3333   -20
DEB-AA  4444   20

Currently my output is this:

DEBNUM        DEBAMT   CREDNUM       CREDAMT
DEB-AA 1111   50       NULL          NULL
NULL          NULL     CRED-AA 2222  -50
NULL          NULL     CRED-AA 3333  -20
DEB-AA 4444   20       NULL          NULL

From this query:

SELECT 
CASE WHEN SUBSTR(TYPE, 1, 3) = 'DEB' THEN TYPE||' '|| BNO ELSE NULL END AS "DEBNUM",
CASE WHEN SUBSTR(TYPE, 1, 3) = 'DEB' THEN AMT ELSE NULL END AS "DEBAMT",
CASE WHEN SUBSTR(TYPE, 1, 4) = 'CRED' THEN TYPE||' '|| BNO ELSE NULL END AS "CREDNUM",
CASE WHEN SUBSTR(TYPE, 1, 4) = 'CRED' THEN AMT ELSE NULL END AS "CREDAMT"
FROM balance

I want my output to look like this:

DEBNUM        DEBAMT   CREDNUM       CREDAMT
DEB-AA 1111   50       CRED-AA 2222  -50
DEB-AA 4444   20       CRED-AA 3333  -20

Now I think it requires a CTE but I cant get it to run with one as i dont have that much experience creating them.

EDIT

When introducing this to larger sets of data that dont have both a CRED and a DEB it is pulling the data back correctly. but it is not linking the rows that do have both a deb and cred values. For example

Current Output:

DEBNUM        DEBAMT   CREDNUM       CREDAMT
DEB-AA 6666   80       CR-QS 2222   -50
DEB-AA 5555   150      CR-QS 4444   -20
DEB-AA 7777   70        
DEB-AA 8888   200       
DEB-AA 9999   60    
DEB-AA 1111   50    
DEB-AA 3333   20

Desired Output:

DEBNUM        DEBAMT   CREDNUM       CREDAMT
DEB-AA 1111   50       CR-QS 2222   -50
DEB-AA 3333   20       CR-QS 4444   -20
DEB-AA 7777   70       NULL         NULL
DEB-AA 8888   200      NULL         NULL
DEB-AA 9999   60       NULL         NULL
DEB-AA 6666   80       NULL         NULL
DEB-AA 5555   150      NULL         NULL

Now i think this can be achieved by using an extra field which links a CRED to a DEB. Here is an updated sample data:

TYPE    BNO   AMT  DEBBNO
DEB-AA  1111  50   NULL
CRED-AA 2222  -50  1111
CRED-AA 3333  -20  4444
DEB-AA  4444  20   NULL
DEB-AA  7777  70   NULL
DEB-AA  8888  200  NULL
DEB-AA  9999  60   NULL 
DEB-AA  6666  80   NULL  
DEB-AA  5555  150  NULL

Hi @Ben, this doesn't require a pivot, ) I have tried a pivot and didn't get my required output, hence asking for another approach possibly involving CTE's. — Matt, Oct 06 '15 at 12:24
Fiddle is not working for me so I need to paste answer here: **DATA**: `CREATE TABLE balance( TYPE VARCHAR(18) NOT NULL ,BNO INTEGER NOT NULL ,AMT INTEGER NOT NULL ); INSERT INTO balance(TYPE,BNO,AMT) VALUES ('DEB-AA',1111,50); INSERT INTO balance(TYPE,BNO,AMT) VALUES ('CRED-AA',2222,-50); INSERT INTO balance(TYPE,BNO,AMT) VALUES ('CRED-AA',3333,-20); INSERT INTO balance(TYPE,BNO,AMT) VALUES ('DEB-AA',4444,20); ` — Lukasz Szozda, Oct 06 '15 at 12:35
**Query**: `WITH deb AS ( SELECT TYPE || ' ' || BNO AS DEBNUM ,AMT AS DEBAMT ,ROW_NUMBER() OVER(ORDER BY BNO ASC) AS rn FROM balance WHERE TYPE LIKE 'DEB%' ), cred AS ( SELECT TYPE || ' ' || BNO AS CREDNUM ,AMT AS CREDAMT ,ROW_NUMBER() OVER(ORDER BY BNO ASC) AS rn FROM balance WHERE TYPE LIKE 'CRED%' ) SELECT d.DEBNUM, d.DEBAMT, c.CREDNUM, c.CREDAMT FROM deb d JOIN cred c ON d.rn = c.rn;` — Lukasz Szozda, Oct 06 '15 at 12:35
Finally: **[SqlFiddleDemo](http://sqlfiddle.com/#!4/d2945/1/0)** without `PIVOT`, using `CTE`. Keep in mind that if table contains different number of `DEB/CRED` you may need `OUTER JOIN` — Lukasz Szozda, Oct 06 '15 at 12:37
@lad2025 absolutely spot on, when this gets re opened, post it as the answer and i will mark it as correct :) — Matt, Oct 06 '15 at 12:46

Lukasz Szozda · Accepted Answer · 2015-10-06T13:25:44.180

Data:

CREATE TABLE balance(
   TYPE VARCHAR(18) NOT NULL 
  ,BNO  INTEGER  NOT NULL
  ,AMT  INTEGER  NOT NULL
);
INSERT INTO balance(TYPE,BNO,AMT) VALUES ('DEB-AA',1111,50);
INSERT INTO balance(TYPE,BNO,AMT) VALUES ('CRED-AA',2222,-50);
INSERT INTO balance(TYPE,BNO,AMT) VALUES ('CRED-AA',3333,-20);
INSERT INTO balance(TYPE,BNO,AMT) VALUES ('DEB-AA',4444,20);

Query:

WITH deb AS
(
  SELECT 
     TYPE || ' ' ||  BNO AS DEBNUM
    ,AMT AS DEBAMT
    ,ROW_NUMBER() OVER(ORDER BY BNO ASC) AS rn
  FROM balance
  WHERE TYPE LIKE 'DEB%'
), cred AS
(
  SELECT 
     TYPE || ' ' ||  BNO AS CREDNUM
    ,AMT AS CREDAMT
    ,ROW_NUMBER() OVER(ORDER BY BNO ASC) AS rn
  FROM balance
  WHERE TYPE LIKE 'CRED%'
)
SELECT d.DEBNUM, d.DEBAMT, c.CREDNUM, c.CREDAMT
FROM deb d
JOIN cred c
  ON d.rn = c.rn;

SqlFiddle Demo

Keep in mind that if table contains different number of DEB/CRED you may need FULL OUTER JOIN.

EDIT:

WITH deb AS
(
  SELECT 
     TYPE || ' ' ||  BNO AS DEBNUM
    ,AMT AS DEBAMT
    ,BNO
  FROM balance
  WHERE TYPE LIKE 'DEB%'
), cred AS
(
  SELECT 
     TYPE || ' ' ||  BNO AS CREDNUM
    ,AMT AS CREDAMT
    ,DEBBNO
  FROM balance
  WHERE TYPE LIKE 'CRED%'
)
SELECT d.DEBNUM, d.DEBAMT, c.CREDNUM, c.CREDAMT
FROM deb d
LEFT JOIN cred c
  ON d.BNO = c.DEBBNO;

SqlFiddleDemo2

One small issue, when introducing this to larger amounts of data where they only have debnum's and debamt's, it is not linking the associated amounts with each other on the ones that do match. they can be linked by LABELID i will updat question now but i think it is a small change — Matt, Oct 06 '15 at 13:00

Oracle SQL get values on the same row using CTE

EDIT

1 Answers1