Link to home
Start Free TrialLog in
Avatar of sbornstein2
sbornstein2

asked on

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.
Avatar of Sean Stuber
Sean Stuber

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?
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.
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
Avatar of sbornstein2

ASKER

it doesnt matter to me if it is platform specific, I tried the COLLECT in oracle but that returns a dataset.
keyword not found on the = sign erik
if I take that empnames = out I am getting a right paren error next to the FOR
the collect way requires a followup procedure to parse the set and concatenate the results
right that is why I want to stay away from the collect sd
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;
STRAGG is not being recognized in my query

SELECT
BILLRUN, STRAGG(CUS_PARENT)
FROM BILLRUN.RPT_CUSTOM_XLS
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
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
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
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
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'
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
this was perfect, thanks so much for everyone's help I appreciate the time spent and it was a life saver for me.
glad I could help