We help IT Professionals succeed at work.

Oracle/SQL - comma string comma needed

Hello, I am trying to put together a last column of my resultset something similar to stragg but I am looking for a simple function or within the query to handle this possibly.  I have the following:


Table BillAccts

Billrun    Account
C1         12345
C1         67894
C2         15566
C2         54254

I want to return
C1       12345,67894
C2       15566,54254

So I want to combine in a comma seperated string all the accounts per billrun

Thanks all.
Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
you've posted to Oracle ans MS SQL zones,  do you need a single answer that works on both platforms?  or are platform specific answers ok?

what versions of each platform?

are you only evergoing to have 2 values to concatenate or could it be big?
if big, how big?  longer than a varchar2 can hold?
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
for oracle, if you have stragg, I would just use that.  There are more efficient ways to do it but stragg is by far the simplest and plent fast enough for most purposes.
erikTsomikSystem Architect, CF programmer

Commented:
try this
SELECT DISTINCT
Billrun,
EmpNames = substring( ( SELECT ', ' + account FROM BillAccts e2
WHERE e2.Billrun = e1.Billrun FOR XML path(''), elements
),2,500)
FROM BillAccts e1

Author

Commented:
it doesnt matter to me if it is platform specific, I tried the COLLECT in oracle but that returns a dataset.

Author

Commented:
keyword not found on the = sign erik

Author

Commented:
if I take that empnames = out I am getting a right paren error next to the FOR
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
the collect way requires a followup procedure to parse the set and concatenate the results

Author

Commented:
right that is why I want to stay away from the collect sd
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
if you have 10g or later, and don't want to create your own procedure/function then try this...

SELECT   billrun,
         LTRIM(EXTRACT(XMLAGG(XMLELEMENT("V", ',' || account)),
                       '/V/text()'
                      ),
               ','
              ) AS STRING
    FROM billaccts
GROUP BY billrun;

Author

Commented:
STRAGG is not being recognized in my query

SELECT
BILLRUN, STRAGG(CUS_PARENT)
FROM BILLRUN.RPT_CUSTOM_XLS

Author

Commented:
SELECT   billrun,
         LTRIM(EXTRACT(XMLAGG(XMLELEMENT("V", ',' || CUS_PARENT)),
                       '/V/text()'
                      ),
               ','
              ) AS STRING
    FROM RPT_CUSTOM_XLS
GROUP BY billrun;

CHARACTER STRING BUFFER TO SMALL ERROR
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
if you're getting a buffer error, then it won't matter if you use the collection method, stragg, xml or hierarchical methods, you have  too many values to concatenate into a varchar2 field
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
since you will be getting "big" results I asked about above you'll have to return a CLOB
here's one way....

SELECT billrun,rtrim
           (replace
                (replace
                     (xmlagg(xmlelement("x", cus_parent).getclobval(),  
                      '<x>',
                      NULL),
                 '</x>',
                 ','),
            ',')
FROM rpt_custom_xls
group by billrun
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
erikTsomik,  
that link points to the reverse solution to this question.
If you already have a comma delimited string  pivot that string into rows.

here the author is looking to convert rows into the comma delimited string

Author

Commented:
SELECT billrun,rtrim
           (replace
                (replace
                     (xmlagg(xmlelement("x", cus_parent).getclobval(),  
                      '<x>',
                      NULL),
                 '</x>',
                 ','),
            ','))
FROM rpt_custom_xls
group by billrun;


ORA-06553: PLS-306: wrong number or types of arguments in call to 'SYS_IXMLAGG'
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
oops sorry, I dropped a parentheses in 24973054


cus_parent).getclobval(),  

should have been

cus_parent)).getclobval(),  

full corrected code below
SELECT billrun,rtrim
           (replace
                (replace
                     (xmlagg(xmlelement("x", cus_parent)).getclobval(), 
                      '<x>',
                      NULL),
                 '</x>',
                 ','),
            ',')
FROM rpt_custom_xls
group by billrun;

Open in new window

Author

Commented:
this was perfect, thanks so much for everyone's help I appreciate the time spent and it was a life saver for me.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
glad I could help