Two row into one row conversition [message #611600] |
Fri, 04 April 2014 05:46 |
na.dharma@gmail.com
Messages: 82 Registered: May 2008 Location: bangalore
|
Member |
|
|
CREATE TABLE CRIS_WAREHOUSE_USER.RF_COUNTRY_LIMITS
(
BE_GROUP_ID VARCHAR2(10 BYTE),
COUNTRY_CODE_ISO_2 VARCHAR2(10 BYTE) NOT NULL,
CLOSE_OF_BUSINESS_KEY NUMBER(22),
LIMIT NUMBER
)
TABLESPACE WAREHOUSE_BIG_DATA
Insert into RF_COUNTRY_LIMITS
(BE_GROUP_ID, COUNTRY_CODE_ISO_2, CLOSE_OF_BUSINESS_KEY, LIMIT)
Values
('123', 'MC', 1220140331, 900000);
Insert into RF_COUNTRY_LIMITS
(BE_GROUP_ID, COUNTRY_CODE_ISO_2, CLOSE_OF_BUSINESS_KEY, LIMIT)
Values
('111', 'MC', 1220140331, 80000);
COMMIT;
123 MC 1220140331 900000
111 MC 1220140331 80000
I want to convert two row into row like this below
MC 900000 , 80000
|
|
|
|
|
|
Re: Two row into one row conversition [message #611607 is a reply to message #611606] |
Fri, 04 April 2014 06:54 |
|
gauravgautam135
Messages: 33 Registered: December 2013
|
Member |
|
|
Hi,
I hope this is what you wanted:
SQL> WITH t1 AS
2 (SELECT 123 AS BE_GROUP_ID, 'MC' AS COUNTRY_CODE_ISO_2, 1220140331 AS CLOSE_OF_BUSINESS_KEY, 90
0000 AS LIMIT_AMT FROM DUAL
3 UNION ALL
4 SELECT 111 AS BE_GROUP_ID, 'MC' AS COUNTRY_CODE_ISO_2, 1220140331 AS CLOSE_OF_BUSINESS_KEY, 80
0000 AS LIMIT_AMT FROM DUAL
5 UNION ALL
6 SELECT 111 AS BE_GROUP_ID, 'MC' AS COUNTRY_CODE_ISO_2, 1220140331 AS CLOSE_OF_BUSINESS_KEY, 70
0000 AS LIMIT_AMT FROM DUAL),
7 t2 AS (SELECT COUNTRY_CODE_ISO_2,
8 LIMIT_AMT,
9 ROW_NUMBER() OVER (PARTITION BY COUNTRY_CODE_ISO_2 ORDER BY LIMIT_AMT) AS curr,
10 ROW_NUMBER() OVER (PARTITION BY COUNTRY_CODE_ISO_2 ORDER BY LIMIT_AMT) -1 AS pre
v
11 FROM t1)
12 SELECT COUNTRY_CODE_ISO_2,
13 LTRIM(MAX(SYS_CONNECT_BY_PATH(LIMIT_AMT,','))
14 KEEP (DENSE_RANK LAST ORDER BY curr),',') AS REQ_OUTPUT
15 FROM t2
16 GROUP BY COUNTRY_CODE_ISO_2
17 CONNECT BY prev = PRIOR curr AND COUNTRY_CODE_ISO_2 = PRIOR COUNTRY_CODE_ISO_2
18 START WITH curr = 1;
CO
--
REQ_OUTPUT
--------------------------------------------------------------------------------
MC
700000,800000,900000
|
|
|
|
|
|
|
Re: Two row into one row conversition [message #611621 is a reply to message #611609] |
Fri, 04 April 2014 10:04 |
|
Michel Cadot
Messages: 68643 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
gauravgautam135 wrote on Fri, 04 April 2014 14:25Ok CookieMonster.
But I am having 10G and if I am not wrong LISTAGG starts from 11G.
Irrelevant answer as OP has 11g.
|
|
|