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  |
 |
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 #585413 is a reply to message #585412] |
Sat, 25 May 2013 01:58  |
 |
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
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%')
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 #585434 is a reply to message #585430] |
Sat, 25 May 2013 05:29 |
|
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 #585462 is a reply to message #585460] |
Sun, 26 May 2013 01:12 |
|
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #585475 is a reply to message #585470] |
Sun, 26 May 2013 04:17 |
|
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 1131 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 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
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 #587630 is a reply to message #585410] |
Mon, 17 June 2013 09:38 |
|
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 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
syedabdulbaqi wrote on Mon, 17 June 2013 10:38For 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 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
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 |
|
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 #587654 is a reply to message #587653] |
Mon, 17 June 2013 14:23 |
|
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 1213 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 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
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.
|
|
|
|
Goto Forum:
Current Time: Fri Aug 15 09:33:16 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
