Home » SQL & PL/SQL » SQL & PL/SQL » Two row into one row conversition (Oralce 11g)
Two row into one row conversition [message #611600] Fri, 04 April 2014 05:46 Go to next message
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 #611604 is a reply to message #611600] Fri, 04 April 2014 06:32 Go to previous messageGo to next message
na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore
Member

anybody can answer my query.
Re: Two row into one row conversition [message #611605 is a reply to message #611604] Fri, 04 April 2014 06:38 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
What column(s) are the two rows supposed to be joined on?
Re: Two row into one row conversition [message #611606 is a reply to message #611604] Fri, 04 April 2014 06:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
This is just a plain GROUP BY with LISTAGG.

SY.
Re: Two row into one row conversition [message #611607 is a reply to message #611606] Fri, 04 April 2014 06:54 Go to previous messageGo to next message
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 #611608 is a reply to message #611607] Fri, 04 April 2014 07:12 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
listagg would be simpler.
Re: Two row into one row conversition [message #611609 is a reply to message #611608] Fri, 04 April 2014 07:25 Go to previous messageGo to next message
gauravgautam135
Messages: 33
Registered: December 2013
Member
Ok CookieMonster.

But I am having 10G and if I am not wrong LISTAGG starts from 11G.
Re: Two row into one row conversition [message #611618 is a reply to message #611609] Fri, 04 April 2014 09:47 Go to previous messageGo to next message
na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore
Member

I have done like this way..Please check

SELECT country_code_iso_2 AS iso,
SUM(DECODE(be_group_id, '111', LIMIT)) AS csi_limit,
SUM(DECODE(be_group_id, '123', LIMIT)) AS cssel_limit
FROM rf_country_limits
GROUP BY country_code_iso_2
Re: Two row into one row conversition [message #611620 is a reply to message #611618] Fri, 04 April 2014 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Does this give you :
Quote:
MC 900000 , 80000


No? So it is not correct.

Re: Two row into one row conversition [message #611621 is a reply to message #611609] Fri, 04 April 2014 10:04 Go to previous message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
gauravgautam135 wrote on Fri, 04 April 2014 14:25
Ok CookieMonster.

But I am having 10G and if I am not wrong LISTAGG starts from 11G.


Irrelevant answer as OP has 11g.


Previous Topic: incorrect output from my sql command
Next Topic: Remove least paid employee who are reporting to BLAKE ?
Goto Forum:
  


Current Time: Tue Apr 23 13:39:51 CDT 2024