Home » SQL & PL/SQL » SQL & PL/SQL » How to construct a SQL query to fetch different rows from same table in different columns? (Oracle 10)
How to construct a SQL query to fetch different rows from same table in different columns? [message #585410] Sat, 25 May 2013 01:24 Go to next message
syedabdulbaqi
Messages: 11
Registered: May 2013
Junior Member
Lets say I have a table in ORACLE database like:

ACC_ID | ACC_AMT
111    | 10000
111    | 12000
111    | 14000
222    | 25000
222    | 30000
333    | 18000
333    | 27000
333    | 13000
333    | 15000

I want to get the output as:

ACC_ID_1 | ACC_AMT_1 | ACC_ID_2 | ACC_AMT_2 | ACC_ID_3 | ACC_AMT_3
111      | 10000     | 222      | 25000     | 333      | 18000
111      | 12000     | 222      | 30000     | 333      | 27000
111      | 14000     | null     | null      | 333      | 13000
null     | null      | null     | null      | 333      | 15000

I need each different ACC_ID with ACC_AMT in different columns. The table may have other different ACC_ID also, but I will fetch only what I need. What is the best way to do this?

So far I have tried this:

SELECT 
(CASE WHEN ACC_ID=111 THEN ACC_ID END) AS ACC_ID_1,
(CASE WHEN ACC_ID=111 THEN ACC_AMT END) AS ACC_AMT_1,
(CASE WHEN ACC_ID=222 THEN ACC_ID END) AS ACC_ID_2,
(CASE WHEN ACC_ID=222 THEN ACC_AMT END) AS ACC_AMT_2,
(CASE WHEN ACC_ID=333 THEN ACC_ID END) AS ACC_ID_3,
(CASE WHEN ACC_ID=333 THEN ACC_AMT END) AS ACC_AMT_3
FROM <TABLE_NAME>

But I am not getting the desired result.
Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #585412 is a reply to message #585410] Sat, 25 May 2013 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data and show you.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #585413 is a reply to message #585412] Sat, 25 May 2013 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from t order by acc_id, acc_amt;
    ACC_ID    ACC_AMT
---------- ----------
       111      10000
       111      12000
       111      14000
       222      25000
       222      30000
       333      13000
       333      15000
       333      18000
       333      27000

9 rows selected.

SQL> with 
  2    data as  (
  3      select acc_id, acc_amt,
  4             dense_rank() over(order by acc_id) rk,
  5             row_number() over(partition by acc_id order by acc_amt) rn
  6      from t
  7    )
  8  select max(decode(rk, 1, acc_id))  acc_id_1,
  9         max(decode(rk, 1, acc_amt)) acc_amt_1,
 10         max(decode(rk, 2, acc_id))  acc_id_2,
 11         max(decode(rk, 2, acc_amt)) acc_amt_2,
 12         max(decode(rk, 3, acc_id))  acc_id_3,
 13         max(decode(rk, 3, acc_amt)) acc_amt_3
 14  from data
 15  group by rn
 16  order by rn
 17  /
  ACC_ID_1  ACC_AMT_1   ACC_ID_2  ACC_AMT_2   ACC_ID_3  ACC_AMT_3
---------- ---------- ---------- ---------- ---------- ----------
       111      10000        222      25000        333      13000
       111      12000        222      30000        333      15000
       111      14000                              333      18000
                                                   333      27000

Regards
Michel
Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #585417 is a reply to message #585413] Sat, 25 May 2013 03:07 Go to previous messageGo to next message
syedabdulbaqi
Messages: 11
Registered: May 2013
Junior Member
Thanks Michel. That's a great start.
But my actual requirement is based on wildcard search.

  
CREATE TABLE "SAB"."TABLE1" 
   (	"ACC_ID" NUMBER, 
	"ACC_AMT" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
REM INSERTING into SAB.TABLE1
SET DEFINE OFF;
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (115,10000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (115,12000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (114,14000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (221,25000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (211,30000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (331,13000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (335,15000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (355,18000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (339,27000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (431,17000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (445,16000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (555,18000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (589,22000);


I want to get fetch rows based upon wildcard search
(ACC_ID_1 => '11%' ACC_ID_2 => '21%' ACC_ID_3 => '33%')
, so that I get the output as:

ACC_ID_1 | ACC_AMT_1 | ACC_ID_2 | ACC_AMT_2 | ACC_ID_3 | ACC_AMT_3
111      | 10000     | 211      | 30000     | 331      | 13000
111      | 12000     | null     | null      | 335      | 15000
111      | 14000     | null     | null      | null     | null
null     | null      | null     | null      | null     | null


Thanks again for your help.
Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #585430 is a reply to message #585417] Sat, 25 May 2013 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want to get fetch rows based upon wildcard search


I don't know what that means and how is this input and how does this impact the result.
You have to be more specific and explain it in words that can be converted into SQL.

Regards
Michel
Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #585434 is a reply to message #585430] Sat, 25 May 2013 05:29 Go to previous messageGo to next message
syedabdulbaqi
Messages: 11
Registered: May 2013
Junior Member
Sorry, if I am causing confusion. Let me again begin from the start.

Suppose I have a table (TABLE1) with columns ACC_ID and ACC_AMT. This table may have indefinite number of ACC_ID (like '115', '114', '221', '211', '331', '335', '355',......so on). My requirement here is to display only some ACC_ID with ACC_AMT each in separate column selected via wildcard search on ACC_ID.

I hope I explained it clearly this time.

If my table structure and data is like:

CREATE TABLE "SAB"."TABLE1" 
   (	"ACC_ID" NUMBER, 
	"ACC_AMT" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
REM INSERTING into SAB.TABLE1
SET DEFINE OFF;
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (115,10000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (115,12000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (114,14000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (221,25000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (211,30000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (331,13000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (335,15000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (355,18000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (339,27000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (431,17000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (445,16000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (555,18000);
Insert into SAB.TABLE1 (ACC_ID,ACC_AMT) values (589,22000);


then I want to select some of the ACC_ID based on wildcard search (lets say ACC_ID_1 => '11%' ACC_ID_2 => '21%' ACC_ID_3 => '33%'), so that I get the output as:

ACC_ID_1 | ACC_AMT_1 | ACC_ID_2 | ACC_AMT_2 | ACC_ID_3 | ACC_AMT_3
115      | 10000     | 211      | 30000     | 331      | 13000
115      | 12000     | null     | null      | 335      | 15000
114      | 14000     | null     | null      | null     | null
null     | null      | null     | null      | null     | null



Thanks.

[Updated on: Sat, 25 May 2013 05:30]

Report message to a moderator

Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #585459 is a reply to message #585434] Sun, 26 May 2013 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Columns in SQL query are static.
There are ways to make it dynamic but it no more a pure SQL query (unless you use some very complex extension).

Regards
Michel
Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #585460 is a reply to message #585459] Sun, 26 May 2013 00:35 Go to previous messageGo to next message
syedabdulbaqi
Messages: 11
Registered: May 2013
Junior Member
I was afraid of getting such answer. Sad
I am not so good in writing SQL queries, can you please help me in this regard?

Thanks.

[Updated on: Sun, 26 May 2013 00:38]

Report message to a moderator

Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #585462 is a reply to message #585460] Sun, 26 May 2013 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry I misread the question, I though you wanted a variable number of columns depending on the condition but you always want 3 columns with the 11% in first column, 22% in the second and 33% in third one (as it seems from your result but you didn't specify it).
It can be done modifying my previous query, here's an example:
SQL> with 
  2    data as  (
  3      select acc_id, acc_amt,
  4             dense_rank() over (order by substr(acc_id,1,2)) rk,
  5             row_number() 
  6               over (partition by substr(acc_id,1,2) order by acc_id, acc_amt) rn
  7      from table1
  8      where acc_id like '11%' or acc_id like '22%' or acc_id like '33%'
  9    )
 10  select max(decode(rk, 1, acc_id))  acc_id_1,
 11         max(decode(rk, 1, acc_amt)) acc_amt_1,
 12         max(decode(rk, 2, acc_id))  acc_id_2,
 13         max(decode(rk, 2, acc_amt)) acc_amt_2,
 14         max(decode(rk, 3, acc_id))  acc_id_3,
 15         max(decode(rk, 3, acc_amt)) acc_amt_3
 16  from data
 17  group by rn
 18  order by rn
 19  /
  ACC_ID_1  ACC_AMT_1   ACC_ID_2  ACC_AMT_2   ACC_ID_3  ACC_AMT_3
---------- ---------- ---------- ---------- ---------- ----------
       114      14000        221      25000        331      13000
       115      10000                              335      15000
       115      12000                              339      27000

Regards
Michel

Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #585466 is a reply to message #585462] Sun, 26 May 2013 02:08 Go to previous messageGo to next message
syedabdulbaqi
Messages: 11
Registered: May 2013
Junior Member
Thanks Michel. This was very helpful.

But I am not getting the desired result when I am using ACC_ID of different length for wildcard search:
where acc_id like '11%' or acc_id like '2%' or acc_id like '33%'


Your solution is working only when all the ACC_ID in wildcard search are of same length.

[Updated on: Sun, 26 May 2013 02:09]

Report message to a moderator

Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #585470 is a reply to message #585466] Sun, 26 May 2013 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, you have to specify in details and in exhaustive way what can be the input and the output.
My query works for the example you gave, as there is no specification the example IS the specification, so if the query works for it is a correct one.
In addition, I said this is an example, you SHOULD be able to modify it to get what you want, you just have to compare the 2 queries I gave.

Regards
Michel
Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #585475 is a reply to message #585470] Sun, 26 May 2013 04:17 Go to previous messageGo to next message
syedabdulbaqi
Messages: 11
Registered: May 2013
Junior Member
You are right, I should have been be more specific in my requirement. But I thought it would be easy to work on simplified test case, and then later implement the same in actual case. But I suppose it didn't worked. Now I will try to explain you the Actual Case, hopefully with all the details.

I have a table (FGBTRNH) with about 50 columns, I am not interested in all the 50 columns but only 4 of them (FGBTRNH_ACCT_CODE, FGBTRNH_TRANS_AMT, FGBTRNH_DR_CR_IND and FGBTRNH_COAS_CODE).

FGBTRNH_ACCT_CODE is referred to ACC_ID and FGBTRNH_TRANS_AMT is referred to ACC_AMT in previous examples.

FGBTRNH_ACCT_CODE have 100s of account numbers of length size of 4 or 5. For example: 1150, 1151, 11511, 5140, 5141, 51410, 5300, 5310, 53110,.......so on.
FGBTRNH_TRANS_AMT is just amount field of NUMBER datatype.
FGBTRNH_DR_CR_IND is single character field and can only take 4 values 'D', 'C', '+' or '-'
FGBTRNH_COAS_CODE is single character field and can only take 2 values 'S' or 'E', I only want records with value 'E'

I want to fetch 18 FGBTRNH_ACCT_CODE from the above table which satisfies these conditions:

FGBTRNH_ACCT_CODE_1 => FGBTRNH_ACCT_CODE LIKE '514%'
FGBTRNH_ACCT_CODE_2 => FGBTRNH_ACCT_CODE LIKE '511%'
FGBTRNH_ACCT_CODE_3 => FGBTRNH_ACCT_CODE LIKE '53%'
FGBTRNH_ACCT_CODE_4 => FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND='C'
FGBTRNH_ACCT_CODE_5 => FGBTRNH_ACCT_CODE LIKE '757%'
FGBTRNH_ACCT_CODE_6 => FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND='D'
.........so on


My desired output is:

ACCT_CODE_1   TRANS_AMT_1	ACCT_CODE_2   TRANS_AMT_2	ACCT_CODE_3   TRANS_AMT_3	ACCT_CODE_4   TRANS_AMT_4   ACCT_CODE_5   TRANS_AMT_5    ACCT_CODE_6   TRANS_AMT_6
5140	      5000	        5110	      1702115.14	5300	      2252400	        16312	      183493344.6   75700	  20631876.11    16312	   1
5141	      135008658.3	5111	      3046875.27	5301	      108366121	        null	      null	    7570	  447500000      16312	   447500000
5141	      135008658.3	5111	      183493344.6	5310	      4809054.99	null	      null	    7570	  1              16312	   135008658.3
5141	      108366121	        51110	      186549.5	        53110	      4902944.99	null	      null	    7571	  1              null	   null
null	      null	        51110	      20631876.11	null	      null	        null	      null	    7572	  1              null	   null
null	      null	        5111	      1	                null	      null	        null	      null	    7573	  101232036      null	   null
null	      null	        5110	      1	                null	      null	        null	      null	    7576	  447500000      null	   null
null	      null	        null	      null	        null	      null	        null	      null	    7570	  5000000        null	   null


Please find the table structure with data in the attachment.
  • Attachment: FGBTRNH.sql
    (Size: 5.63KB, Downloaded 74 times)
Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #585487 is a reply to message #585475] Sun, 26 May 2013 06:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
COLUMN ACCT_CODE_1 FORMAT 9999999999
COLUMN ACCT_CODE_2 FORMAT 9999999999
COLUMN ACCT_CODE_3 FORMAT 9999999999
COLUMN ACCT_CODE_4 FORMAT 9999999999
COLUMN ACCT_CODE_5 FORMAT 9999999999
COLUMN ACCT_CODE_6 FORMAT 9999999999
COLUMN TRANS_AMT_1 FORMAT 999999999.99
COLUMN TRANS_AMT_2 FORMAT 999999999.99
COLUMN TRANS_AMT_3 FORMAT 999999999.99
COLUMN TRANS_AMT_4 FORMAT 999999999.99
COLUMN TRANS_AMT_5 FORMAT 999999999.99
COLUMN TRANS_AMT_6 FORMAT 999999999.99
SET LINESIZE 200
SET NULL "       NULL"
WITH T1 AS (
            SELECT  CASE
                      WHEN FGBTRNH_ACCT_CODE LIKE '514%' THEN 1
                      WHEN FGBTRNH_ACCT_CODE LIKE '511%' THEN 2
                      WHEN FGBTRNH_ACCT_CODE LIKE '53%' THEN 3
                      WHEN FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'C' THEN 4
                      WHEN FGBTRNH_ACCT_CODE LIKE '757%' THEN 5
                      WHEN FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'D' THEN 6
                    END COLUMN_INDICATOR,                      
                    FGBTRNH_ACCT_CODE,
                    FGBTRNH_TRANS_AMT
              FROM  FGBTRNH
            ),
      T2 AS (
            SELECT  T1.*,
                    ROW_NUMBER() OVER(PARTITION BY COLUMN_INDICATOR ORDER BY FGBTRNH_ACCT_CODE,FGBTRNH_TRANS_AMT DESC) RN
              FROM  T1
              WHERE COLUMN_INDICATOR IS NOT NULL
            )
SELECT  MAX(
            CASE COLUMN_INDICATOR
              WHEN 1 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_1,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 1 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_1,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 2 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_2,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 2 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_2,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 3 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_3,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 3 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_3,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 4 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_4,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 4 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_4,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 5 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_5,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 5 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_5,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 6 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_6,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 6 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_6
  FROM  T2
  GROUP BY RN
  ORDER BY ACCT_CODE_1 NULLS LAST,
           ACCT_CODE_2 NULLS LAST,
           ACCT_CODE_3 NULLS LAST,
           ACCT_CODE_4 NULLS LAST,
           ACCT_CODE_5 NULLS LAST,
           ACCT_CODE_6 NULLS LAST
/

ACCT_CODE_1   TRANS_AMT_1 ACCT_CODE_2   TRANS_AMT_2 ACCT_CODE_3   TRANS_AMT_3 ACCT_CODE_4   TRANS_AMT_4 ACCT_CODE_5   TRANS_AMT_5 ACCT_CODE_6   TRANS_AMT_6
----------- ------------- ----------- ------------- ----------- ------------- ----------- ------------- ----------- ------------- ----------- -------------
       5140       5000.00        5110    1702115.14        5300    2252400.00       16312  183493344.60        7570  447500000.00       16312  447500000.00
       5141  135008658.28        5110          1.00        5301  108366121.01        NULL        NULL          7570    5000000.00       16312  135008658.28
       5141  135008658.28        5111  183493344.60        5310    4809054.99        NULL        NULL          7570          1.00       16312          1.00
       5141  108366121.01        5111    3046875.27       53110    4902944.99        NULL        NULL          7571          1.00        NULL        NULL
       NULL        NULL          5111          1.00        NULL        NULL          NULL        NULL          7572          1.00        NULL        NULL
       NULL        NULL         51110   20631876.11        NULL        NULL          NULL        NULL          7573  101232036.00        NULL        NULL
       NULL        NULL         51110     186549.50        NULL        NULL          NULL        NULL          7576  447500000.00        NULL        NULL
       NULL        NULL          NULL        NULL          NULL        NULL          NULL        NULL         75700   20631876.11        NULL        NULL

8 rows selected.

SQL>


SY.

[Updated on: Sun, 26 May 2013 06:59]

Report message to a moderator

Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #585519 is a reply to message #585487] Mon, 27 May 2013 03:59 Go to previous messageGo to next message
syedabdulbaqi
Messages: 11
Registered: May 2013
Junior Member
Thanks Solomon. That worked perfectly.
Thanks to you too Michel, your help from start was really worth appreciating.


Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #587630 is a reply to message #585410] Mon, 17 June 2013 09:38 Go to previous messageGo to next message
syedabdulbaqi
Messages: 11
Registered: May 2013
Junior Member
Last solution suggested by Solomon is not working when I want to display same FGBTRNH_ACCT_CODE in more than one column.

For example the last condition (WHEN FGBTRNH_ACCT_CODE LIKE '16312%' THEN 7) will never be evaluated because of the following 2 prior conditions:
WHEN FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'C' THEN 4
WHEN FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'D' THEN 6


Snippet from last solution:
....
WITH T1 AS (
SELECT  CASE
                      WHEN FGBTRNH_ACCT_CODE LIKE '514%' THEN 1
                      WHEN FGBTRNH_ACCT_CODE LIKE '511%' THEN 2
                      WHEN FGBTRNH_ACCT_CODE LIKE '53%' THEN 3
                      WHEN FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'C' THEN 4
                      WHEN FGBTRNH_ACCT_CODE LIKE '757%' THEN 5
                      WHEN FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'D' THEN 6
                      WHEN FGBTRNH_ACCT_CODE LIKE '16312%' THEN 7
                    END COLUMN_INDICATOR,                      
                    FGBTRNH_ACCT_CODE,
                    FGBTRNH_TRANS_AMT
              FROM  FGBTRNH
....


I tried by adding a separate CASE statement, but I could figure out what changes to be made in T2. Still not getting desired result.

....
WITH T1 AS (
SELECT  CASE
                      WHEN FGBTRNH_ACCT_CODE LIKE '514%' THEN 1
                      WHEN FGBTRNH_ACCT_CODE LIKE '511%' THEN 2
                      WHEN FGBTRNH_ACCT_CODE LIKE '53%' THEN 3
                      WHEN FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'C' THEN 4
                      WHEN FGBTRNH_ACCT_CODE LIKE '757%' THEN 5
                      WHEN FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'D' THEN 6
                    END COLUMN_INDICATOR,                      
        CASE
                      WHEN FGBTRNH_ACCT_CODE LIKE '16312%' THEN 7
                    END COLUMN_INDICATOR2,
                    FGBTRNH_ACCT_CODE,
                    FGBTRNH_TRANS_AMT
              FROM  FGBTRNH
              ),
      T2 AS (
            SELECT  T1.*,
                    ROW_NUMBER() OVER(PARTITION BY COLUMN_INDICATOR ORDER BY FGBTRNH_ACCT_CODE,FGBTRNH_TRANS_AMT DESC) RN
              FROM  T1
              WHERE COLUMN_INDICATOR IS NOT NULL
            )
....
Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #587638 is a reply to message #587630] Mon, 17 June 2013 11:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
syedabdulbaqi wrote on Mon, 17 June 2013 10:38
For example the last condition (WHEN FGBTRNH_ACCT_CODE LIKE '16312%' THEN 7) will never be evaluated because of the following 2 prior conditions


Not true. CASE is evalueated til it hits a match. So last WHEN clause:

WHEN FGBTRNH_ACCT_CODE LIKE '16312%' THEN 7


in code

....
WITH T1 AS (
SELECT  CASE
                      WHEN FGBTRNH_ACCT_CODE LIKE '514%' THEN 1
                      WHEN FGBTRNH_ACCT_CODE LIKE '511%' THEN 2
                      WHEN FGBTRNH_ACCT_CODE LIKE '53%' THEN 3
                      WHEN FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'C' THEN 4
                      WHEN FGBTRNH_ACCT_CODE LIKE '757%' THEN 5
                      WHEN FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'D' THEN 6
                      WHEN FGBTRNH_ACCT_CODE LIKE '16312%' THEN 7
                    END COLUMN_INDICATOR,                      
                    FGBTRNH_ACCT_CODE,
                    FGBTRNH_TRANS_AMT
              FROM  FGBTRNH
....


will be honored when FGBTRNH_ACCT_CODE is like '16312%' and FGBTRNH_DR_CR_IND is neither 'C' nor 'D'. You need to explain (in words) what you wnat to display in ACCT_CODE_7 & TRANS_AMT_7.

SY.
Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #587639 is a reply to message #587638] Mon, 17 June 2013 11:09 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
Or, perhaps, you want:

WITH T1 AS (
            SELECT  CASE
                      WHEN FGBTRNH_ACCT_CODE LIKE '514%' THEN 1
                      WHEN FGBTRNH_ACCT_CODE LIKE '511%' THEN 2
                      WHEN FGBTRNH_ACCT_CODE LIKE '53%' THEN 3
                      WHEN FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'C' THEN 4
                      WHEN FGBTRNH_ACCT_CODE LIKE '757%' THEN 5
                      WHEN FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'D' THEN 6
                    END COLUMN_INDICATOR,
                    FGBTRNH_ACCT_CODE,
                    FGBTRNH_TRANS_AMT
              FROM  FGBTRNH
            ),
      T2 AS (
            SELECT  T1.*,
                    ROW_NUMBER() OVER(PARTITION BY COLUMN_INDICATOR ORDER BY FGBTRNH_ACCT_CODE,FGBTRNH_TRANS_AMT DESC) RN
              FROM  T1
              WHERE COLUMN_INDICATOR IS NOT NULL
            )
SELECT  MAX(
            CASE COLUMN_INDICATOR
              WHEN 1 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_1,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 1 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_1,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 2 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_2,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 2 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_2,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 3 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_3,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 3 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_3,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 4 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_4,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 4 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_4,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 5 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_5,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 5 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_5,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 6 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_6,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 6 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_6,
        MAX(
            CASE
              WHEN COLUMN_INDICATOR IN (4,6) THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_7,
        MAX(
            CASE
              WHEN COLUMN_INDICATOR IN (4,6) THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_7
  FROM  T2
  GROUP BY RN
  ORDER BY ACCT_CODE_1 NULLS LAST,
           ACCT_CODE_2 NULLS LAST,
           ACCT_CODE_3 NULLS LAST,
           ACCT_CODE_4 NULLS LAST,
           ACCT_CODE_5 NULLS LAST,
           ACCT_CODE_6 NULLS LAST
/

ACCT_CODE_1   TRANS_AMT_1 ACCT_CODE_2   TRANS_AMT_2 ACCT_CODE_3   TRANS_AMT_3 ACCT_CODE_4   TRANS_AMT_4 ACCT_CODE_5   TRANS_AMT_5 ACCT_CODE_6   TRANS_AMT_6 ACCT_CODE_7   TRANS_AMT_7
----------- ------------- ----------- ------------- ----------- ------------- ----------- ------------- ----------- ------------- ----------- ------------- ----------- -------------
       5140       5000.00        5110    1702115.14        5300    2252400.00       16312  183493344.60        7570  447500000.00       16312  447500000.00       16312  447500000.00
       5141  135008658.28        5110          1.00        5301  108366121.01        NULL        NULL          7570    5000000.00       16312  135008658.28       16312  135008658.28
       5141  135008658.28        5111  183493344.60        5310    4809054.99        NULL        NULL          7570          1.00       16312          1.00       16312          1.00
       5141  108366121.01        5111    3046875.27       53110    4902944.99        NULL        NULL          7571          1.00        NULL        NULL          NULL        NULL
       NULL        NULL          5111          1.00        NULL        NULL          NULL        NULL          7572          1.00        NULL        NULL          NULL        NULL
       NULL        NULL         51110   20631876.11        NULL        NULL          NULL        NULL          7573  101232036.00        NULL        NULL          NULL        NULL
       NULL        NULL         51110     186549.50        NULL        NULL          NULL        NULL          7576  447500000.00        NULL        NULL          NULL        NULL
       NULL        NULL          NULL        NULL          NULL        NULL          NULL        NULL         75700   20631876.11        NULL        NULL          NULL        NULL

8 rows selected.

SQL>


SY.

[Updated on: Mon, 17 June 2013 11:11]

Report message to a moderator

Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #587640 is a reply to message #587638] Mon, 17 June 2013 11:14 Go to previous messageGo to next message
syedabdulbaqi
Messages: 11
Registered: May 2013
Junior Member
You are absolutely right! CASE will be evaluate till it hits a match, which is fine.
But in my scenario, I want 2 similar CASE conditions to get evaluated, so that I can display them in separate columns.

I want to display certain FGBTRNH_ACCT_CODE (Ex: FGBTRNH_ACCT_CODE LIKE '16312%') in one column, and I also wish to display same FGBTRNH_ACCT_CODE in separate column with some extra condition (FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'D').

If the first condition is a match, then CASE will not evaluate the second and third even though there is an extra condition.
WHEN FGBTRNH_ACCT_CODE LIKE '16312%' THEN 7
WHEN FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'C' THEN 4
WHEN FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'D' THEN 6

Hope I stated it clearly!

[Updated on: Mon, 17 June 2013 11:25]

Report message to a moderator

Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #587647 is a reply to message #587640] Mon, 17 June 2013 12:55 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
And I gave you an answer. All you write in CASE statement is atomic conditions and then you consolidate them the way you need in combined column(s). In your partiticular example COLUMN_INDICATOR IN (4,6) is all you need for additional column.

SY.

[Updated on: Mon, 17 June 2013 12:55]

Report message to a moderator

Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #587652 is a reply to message #587639] Mon, 17 June 2013 13:49 Go to previous messageGo to next message
syedabdulbaqi
Messages: 11
Registered: May 2013
Junior Member
Nice trick. I understood what you mean.
But due to some weird reason IN clause is only fetching from one COLUMN_INDICATOR. Even in the last example which you have given notice that last 2 columns (ACCT_CODE_7 and TRANS_AMT_7) should have 4 records of ACCT_CODE_4 and ACCT_CODE_6 combined, but it shows only 3 records of ACCT_CODE_6.

Is it due to the MAX function?
Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #587653 is a reply to message #587652] Mon, 17 June 2013 13:52 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.
Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #587654 is a reply to message #587653] Mon, 17 June 2013 14:23 Go to previous messageGo to next message
syedabdulbaqi
Messages: 11
Registered: May 2013
Junior Member
Dear BlackSwan,

I have a table (FGBTRNH) with about 50 columns, I am not interested in all the 50 columns but only 4 of them (FGBTRNH_ACCT_CODE, FGBTRNH_TRANS_AMT, FGBTRNH_DR_CR_IND and FGBTRNH_COAS_CODE). Please find table structure in attachment.

FGBTRNH_ACCT_CODE have 100s of account numbers of length size of 4 or 5. For example: 1150, 1151, 11511, 5140, 5141, 51410, 5300, 5310, 53110,.......so on.
FGBTRNH_TRANS_AMT is just amount field of NUMBER datatype.
FGBTRNH_DR_CR_IND is single character field and can only take 4 values 'D', 'C', '+' or '-'
FGBTRNH_COAS_CODE is single character field and can only take 2 values 'S' or 'E', I only want records with value 'E'

I want to fetch 18 FGBTRNH_ACCT_CODE based on some conditions from the above table and display them in separate columns.

Last solution suggested by Solomon is:

WITH T1 AS (
            SELECT  CASE
                      WHEN FGBTRNH_ACCT_CODE LIKE '514%' THEN 1
                      WHEN FGBTRNH_ACCT_CODE LIKE '511%' THEN 2
                      WHEN FGBTRNH_ACCT_CODE LIKE '53%' THEN 3
                      WHEN FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'C' THEN 4
                      WHEN FGBTRNH_ACCT_CODE LIKE '757%' THEN 5
                      WHEN FGBTRNH_ACCT_CODE LIKE '16312%' AND FGBTRNH_DR_CR_IND = 'D' THEN 6
                    END COLUMN_INDICATOR,
                    FGBTRNH_ACCT_CODE,
                    FGBTRNH_TRANS_AMT
              FROM  FGBTRNH
            ),
      T2 AS (
            SELECT  T1.*,
                    ROW_NUMBER() OVER(PARTITION BY COLUMN_INDICATOR ORDER BY FGBTRNH_ACCT_CODE,FGBTRNH_TRANS_AMT DESC) RN
              FROM  T1
              WHERE COLUMN_INDICATOR IS NOT NULL
            )
SELECT  MAX(
            CASE COLUMN_INDICATOR
              WHEN 1 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_1,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 1 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_1,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 2 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_2,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 2 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_2,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 3 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_3,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 3 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_3,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 4 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_4,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 4 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_4,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 5 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_5,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 5 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_5,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 6 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_6,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 6 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_6,
        MAX(
            CASE
              WHEN COLUMN_INDICATOR IN (4,6) THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_7,
        MAX(
            CASE
              WHEN COLUMN_INDICATOR IN (4,6) THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_7
  FROM  T2
  GROUP BY RN
  ORDER BY ACCT_CODE_1 NULLS LAST,
           ACCT_CODE_2 NULLS LAST,
           ACCT_CODE_3 NULLS LAST,
           ACCT_CODE_4 NULLS LAST,
           ACCT_CODE_5 NULLS LAST,
           ACCT_CODE_6 NULLS LAST
/


In the expected output the last column (TRANS_AMT_7) should have combined records of (TRANS_AMT_4 and TRANS_AMT_6). But due to some weird reason IN clause is only fetching (TRANS_AMT_6), not (TRANS_AMT_4).

I hope you get the whole picture now.
  • Attachment: FGBTRNH.sql
    (Size: 5.63KB, Downloaded 29 times)
Re: How to construct a SQL query to fetch different rows from same table in different columns? [message #588207 is a reply to message #587654] Sun, 23 June 2013 10:55 Go to previous message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
I think now I understand what you are trying to do. It is not that easy, but you could do something like:

COLUMN ACCT_CODE_1 FORMAT 9999999999
COLUMN ACCT_CODE_2 FORMAT 9999999999
COLUMN ACCT_CODE_3 FORMAT 9999999999
COLUMN ACCT_CODE_4 FORMAT 9999999999
COLUMN ACCT_CODE_5 FORMAT 9999999999
COLUMN ACCT_CODE_6 FORMAT 9999999999
COLUMN ACCT_CODE_7 FORMAT 9999999999
COLUMN TRANS_AMT_1 FORMAT 999999999.99
COLUMN TRANS_AMT_2 FORMAT 999999999.99
COLUMN TRANS_AMT_3 FORMAT 999999999.99
COLUMN TRANS_AMT_4 FORMAT 999999999.99
COLUMN TRANS_AMT_5 FORMAT 999999999.99
COLUMN TRANS_AMT_6 FORMAT 999999999.99
COLUMN TRANS_AMT_7 FORMAT 999999999.99
SET LINESIZE 200
SET NULL "       NULL"
WITH T1 AS (
            SELECT  CASE
                      WHEN FGBTRNH_ACCT_CODE LIKE '514%' THEN 1
                      WHEN FGBTRNH_ACCT_CODE LIKE '511%' THEN 2
                      WHEN FGBTRNH_ACCT_CODE LIKE '53%' THEN 3
                      WHEN FGBTRNH_ACCT_CODE LIKE '16312%'THEN 7
                      WHEN FGBTRNH_ACCT_CODE LIKE '757%' THEN 5
                    END COLUMN_INDICATOR,                      
                    FGBTRNH_ACCT_CODE,
                    FGBTRNH_TRANS_AMT,
                    FGBTRNH_DR_CR_IND
              FROM  FGBTRNH
            ),
      T2 AS (
            SELECT  T1.*,
                    ROW_NUMBER() OVER(PARTITION BY COLUMN_INDICATOR ORDER BY FGBTRNH_ACCT_CODE,FGBTRNH_TRANS_AMT DESC) RN,
                    COUNT(CASE FGBTRNH_DR_CR_IND WHEN 'C' THEN 1 END) OVER(PARTITION BY COLUMN_INDICATOR) C_CNT,
                    COUNT(CASE FGBTRNH_DR_CR_IND WHEN 'D' THEN 1 END) OVER(PARTITION BY COLUMN_INDICATOR) D_CNT
              FROM  T1
              WHERE COLUMN_INDICATOR IS NOT NULL
            ),
      T3 AS (
            SELECT  T2.*,
                    CASE COLUMN_INDICATOR
                      WHEN 7 THEN CASE
                                    WHEN RN <= C_CNT THEN NTH_VALUE(
                                                                    CASE FGBTRNH_DR_CR_IND
                                                                      WHEN 'C' THEN FGBTRNH_ACCT_CODE
                                                                    END,
                                                                    RN
                                                                   )
                                                            FROM LAST
                                                            IGNORE NULLS
                                                            OVER(
                                                                 PARTITION BY COLUMN_INDICATOR
                                                                 ORDER BY RN DESC
                                                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                                                                )
                                  END
                    END ACCT_CODE_4,
                    CASE COLUMN_INDICATOR
                      WHEN 7 THEN CASE
                                    WHEN RN <= C_CNT THEN NTH_VALUE(
                                                                    CASE FGBTRNH_DR_CR_IND
                                                                      WHEN 'C' THEN FGBTRNH_TRANS_AMT
                                                                    END,
                                                                    RN
                                                                   )
                                                            FROM LAST
                                                            IGNORE NULLS
                                                            OVER(
                                                                 PARTITION BY COLUMN_INDICATOR
                                                                 ORDER BY RN DESC
                                                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                                                                )
                                  END
                    END TRANS_AMT_4,
                    CASE COLUMN_INDICATOR
                      WHEN 7 THEN CASE
                                    WHEN RN <= D_CNT THEN NTH_VALUE(
                                                                    CASE FGBTRNH_DR_CR_IND
                                                                      WHEN 'D' THEN FGBTRNH_ACCT_CODE
                                                                    END,
                                                                    RN
                                                                   )
                                                            FROM LAST
                                                            IGNORE NULLS
                                                            OVER(
                                                                 PARTITION BY COLUMN_INDICATOR
                                                                 ORDER BY RN DESC
                                                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                                                                )
                                  END
                    END ACCT_CODE_6,
                    CASE COLUMN_INDICATOR
                      WHEN 7 THEN CASE
                                    WHEN RN <= D_CNT THEN NTH_VALUE(
                                                                    CASE FGBTRNH_DR_CR_IND
                                                                      WHEN 'D' THEN FGBTRNH_TRANS_AMT
                                                                    END,
                                                                    RN
                                                                   )
                                                            FROM LAST
                                                            IGNORE NULLS
                                                            OVER(
                                                                 PARTITION BY COLUMN_INDICATOR
                                                                 ORDER BY RN DESC
                                                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                                                                )
                                  END
                    END TRANS_AMT_6
              FROM  T2
            )
SELECT  MAX(
            CASE COLUMN_INDICATOR
              WHEN 1 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_1,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 1 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_1,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 2 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_2,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 2 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_2,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 3 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_3,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 3 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_3,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 7 THEN ACCT_CODE_4
            END
           ) ACCT_CODE_4,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 7 THEN TRANS_AMT_4
            END
           ) TRANS_AMT_4,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 5 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_5,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 5 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_5,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 7 THEN ACCT_CODE_6
            END
           ) ACCT_CODE_6,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 7 THEN TRANS_AMT_6
            END
           ) TRANS_AMT_6,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 7 THEN FGBTRNH_ACCT_CODE
            END
           ) ACCT_CODE_7,
        MAX(
            CASE COLUMN_INDICATOR
              WHEN 7 THEN FGBTRNH_TRANS_AMT
            END
           ) TRANS_AMT_7
  FROM  T3
  GROUP BY RN
  ORDER BY ACCT_CODE_1 NULLS LAST,
           ACCT_CODE_2 NULLS LAST,
           ACCT_CODE_3 NULLS LAST,
           ACCT_CODE_4 NULLS LAST,
           ACCT_CODE_5 NULLS LAST,
           ACCT_CODE_6 NULLS LAST,
           ACCT_CODE_7 NULLS LAST
/

ACCT_CODE_1   TRANS_AMT_1 ACCT_CODE_2   TRANS_AMT_2 ACCT_CODE_3   TRANS_AMT_3 ACCT_CODE_4   TRANS_AMT_4 ACCT_CODE_5   TRANS_AMT_5 ACCT_CODE_6   TRANS_AMT_6 ACCT_CODE_7   TRANS_AMT_7
----------- ------------- ----------- ------------- ----------- ------------- ----------- ------------- ----------- ------------- ----------- ------------- ----------- -------------
       5140       5000.00        5110    1702115.14        5300    2252400.00       16312  183493344.60        7570  447500000.00       16312  447500000.00       16312  447500000.00
       5141  135008658.28        5110          1.00        5301  108366121.01        NULL        NULL          7570    5000000.00       16312  135008658.28       16312  183493344.60
       5141  135008658.28        5111  183493344.60        5310    4809054.99        NULL        NULL          7570          1.00       16312          1.00       16312  135008658.28
       5141  108366121.01        5111    3046875.27       53110    4902944.99        NULL        NULL          7571          1.00        NULL        NULL         16312          1.00
       NULL        NULL          5111          1.00        NULL        NULL          NULL        NULL          7572          1.00        NULL        NULL          NULL        NULL
       NULL        NULL         51110   20631876.11        NULL        NULL          NULL        NULL          7573  101232036.00        NULL        NULL          NULL        NULL
       NULL        NULL         51110     186549.50        NULL        NULL          NULL        NULL          7576  447500000.00        NULL        NULL          NULL        NULL
       NULL        NULL          NULL        NULL          NULL        NULL          NULL        NULL         75700   20631876.11        NULL        NULL          NULL        NULL

8 rows selected.

SQL>


SY.
Previous Topic: Column displaying date and time
Next Topic: FIND
Goto Forum:
  


Current Time: Sat Aug 30 21:52:12 CDT 2014

Total time taken to generate the page: 0.13231 seconds