Home » SQL & PL/SQL » SQL & PL/SQL » how to make the value with comma separated values by grouping (oracle 10g)
how to make the value with comma separated values by grouping [message #418294] Thu, 13 August 2009 22:07 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
i have one requirement.just assume that the data is like this.i need to bring the salary with comma separated values like(1000,3000,1200) by grouping doc_no;

doc_no         salary
A1      1000
A2                 2000 
A1      3000
A2                 1000
A1                 1200


final output will be

doc_no   salary
A1      1000,3000,1200
A2      2000,100 
Re: how to make the value with comma separated values by grouping [message #418295 is a reply to message #418294] Thu, 13 August 2009 22:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SEARCH this forum for PIVOT
Re: how to make the value with comma separated values by grouping [message #418312 is a reply to message #418294] Fri, 14 August 2009 01:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
STRAGG will also do exactly what you want.
Re: how to make the value with comma separated values by grouping [message #418328 is a reply to message #418294] Fri, 14 August 2009 03:40 Go to previous messageGo to next message
nagle.yogesh
Messages: 1
Registered: August 2008
Location: Mumbai
Junior Member

Hi,
Pls try this...Hope! It will sort out your problem.

CREATE TABLE EMP2 (EMPCODE NUMBER(Cool,EMPNAME VARCHAR2(100),EMPDEPTNO NUMBER(Cool,EMPSAL NUMBER(34,2));
/
INSERT INTO EMP2 VALUES (100,'JHETA LAL',20,1500);
INSERT INTO EMP2 VALUES (101,'OMPRAKSH',20,6000);
INSERT INTO EMP2 VALUES (102,'PARVEZ KHAN',20,7000);
INSERT INTO EMP2 VALUES (103,'AKBAR KHAN',20,8000);
INSERT INTO EMP2 VALUES (104,'RAHUL KHAN',20,9000);
INSERT INTO EMP2 VALUES (105,'TUSHAR SARODE',10,1000);
INSERT INTO EMP2 VALUES (106,'MANOJ PAREEK',10,2000);
INSERT INTO EMP2 VALUES (107,'HEMANT DAS',10,4000);
INSERT INTO EMP2 VALUES (108,'YOGESH NAGLE',10,5000);
/

SELECT EMPDEPTNO,LTRIM(MAX(SYS_CONNECT_BY_PATH(EMPSAL,',')),',') SAL
FROM (
SELECT EMPDEPTNO ,EMPSAL ,ROW_NUMBER()OVER(ORDER BY E.EMPCODE) RN FROM EMP2 E)
START WITH RN = 1
CONNECT BY PRIOR RN = RN - 1
GROUP BY EMPDEPTNO
ORDER BY EMPDEPTNO;
/
DROP TABLE EMP2 PURGE;

Thanks
Yogesh Nagle.
India.
Re: how to make the value with comma separated values by grouping [message #418329 is a reply to message #418328] Fri, 14 August 2009 03:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I put it to you that STRAGG produces a significantly more understandable, maintainable and smaller piece of code:
create table test_041 (doc_no  varchar2(10),salary number);
insert into test_041 values ('A1',      1000);
insert into test_041 values ('A2',      2000);
insert into test_041 values ('A1',      3000);
insert into test_041 values ('A2',      1000);
insert into test_041 values ('A1',      1200);

select doc_no,stragg(salary) 
from   test_041
group by doc_no;
Re: how to make the value with comma separated values by grouping [message #418332 is a reply to message #418329] Fri, 14 August 2009 04:01 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
its saying STRAGG is invalid identifier ;
Re: how to make the value with comma separated values by grouping [message #418337 is a reply to message #418332] Fri, 14 August 2009 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
chaituu wrote on Fri, 14 August 2009 11:01
its saying STRAGG is invalid identifier ;

Maybe you should click on the link JRowbottom provided. Maybe.

Regards
Michel
Re: how to make the value with comma separated values by grouping [message #418672 is a reply to message #418294] Tue, 18 August 2009 01:39 Go to previous messageGo to next message
deepankar_04
Messages: 28
Registered: August 2009
Location: Udaipur, Rajasthan, INDIA
Junior Member
Dear u can use cursor,

for example:

DECLARE
   v_doc_no emp_doc_table.doc_no%TYPE := null;
   v_salary VARCHAR2(5000);                 -- this is assumable;

   CURSOR m_series IS SELECT doc_no, salary  FROM emp_doc_table 
   GROUP BY doc_no,salary;
BEGIN
   FOR a IN m_series LOOP        
     IF a.doc_no = v_doc_no then  /* Please rectify a bug here */
        v_salary := v_salary||a.salary ||','; /* Here apply trim for removing comma */
     ELSIF v_salary IS NOT NULL THEN  
        DBMS_OUTPUT.Put_Line(v_doc_no||' : '||v_salary||CHR(10));
        v_salary := NULL;
     END IF;  
     v_doc_no := a.doc_no; 
   END LOOP;   
END;


Re: how to make the value with comma separated values by grouping [message #418678 is a reply to message #418672] Tue, 18 August 2009 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also use a .NET program; I think I beat you in finding the worst way to do it.

Regards
Michel
Re: how to make the value with comma separated values by grouping [message #418680 is a reply to message #418672] Tue, 18 August 2009 02:12 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Can try this ...
I am using JRowbottom's table creation .
Not able to get stragg link .


create table test_041 (doc_no  varchar2(10),salary number);
insert into test_041 values ('A1',      1000);
insert into test_041 values ('A2',      2000);
insert into test_041 values ('A1',      3000);
insert into test_041 values ('A2',      1000);
insert into test_041 values ('A1',      1200);

--****----

SELECT doc_no, 
       Substr(comma_list,2) salary_list 
FROM   (SELECT doc_no, 
               salary, 
               Sys_connect_by_path(salary,',') comma_list, 
               rnum, 
               row_count 
        FROM   (SELECT doc_no, 
                       salary, 
                       Row_number() 
                         OVER(PARTITION BY doc_no ORDER BY doc_no) rnum, 
                       Count(* ) 
                         OVER(PARTITION BY doc_no )     row_count 
                FROM   (SELECT doc_no, 
                               salary 
                        FROM   test_041) a) 
        START WITH rnum = 1 
        CONNECT BY doc_no = PRIOR doc_no 
                                  AND rnum = PRIOR rnum + 1) 
WHERE  rnum = row_count 

Re: how to make the value with comma separated values by grouping [message #418694 is a reply to message #418680] Tue, 18 August 2009 03:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you mean 'Can't get STRAGG link.

It's here
or here
or here

Just type STRAGG into google and you too can replace multiple nested queries performing hierarchical operations on analytic functions with a simple group by.
Re: how to make the value with comma separated values by grouping [message #418703 is a reply to message #418694] Tue, 18 August 2009 04:00 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Hi JRowbottom,

Not able to get stragg link..means:

When i click on the links i am getting :
"Your organization's Internet use policy restricts access to this web page at this time." message .

Most of the links are not accessible to me . bad policies ..
Sad


Thanks anyways..Will try those once I go home..

Regards
Prajakta K.

[Updated on: Tue, 18 August 2009 04:01]

Report message to a moderator

Re: how to make the value with comma separated values by grouping [message #418705 is a reply to message #418703] Tue, 18 August 2009 04:18 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Or it can be used by Undocumented function WM_CONCAT.

SQL> select wm_concat(salary) comsep ,doc_no from test_041 group by doc_no;

COMSEP                                             DOC_NO
-------------------------------------------------- ----------
1000,3000,1200                                     A1
2000,1000                                          A2


Thanks
Trivendra
Re: how to make the value with comma separated values by grouping [message #418708 is a reply to message #418705] Tue, 18 August 2009 04:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Question - would you use undocumented features in a production/live system?
Re: how to make the value with comma separated values by grouping [message #418712 is a reply to message #418708] Tue, 18 August 2009 04:53 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Never

I will never recommend to use this in live/production system.

Sad

Thanks
Trivendra
Re: how to make the value with comma separated values by grouping [message #418792 is a reply to message #418712] Tue, 18 August 2009 09:55 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
trivendra wrote on Tue, 18 August 2009 11:53
Never

I will never recommend to use this in live/production system.

As far as you or I can tell, you just did.
Previous Topic: Trunc Creating Problem
Next Topic: Select query while Insert/Update on a table
Goto Forum:
  


Current Time: Fri Dec 09 13:45:11 CST 2016

Total time taken to generate the page: 0.15248 seconds