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.
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.
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
SELECT DISTINCT
Billrun,
EmpNames = substring( ( SELECT ', ' + account FROM BillAccts e2
WHERE e2.Billrun = e1.Billrun FOR XML path(''), elements
),2,500)
FROM BillAccts e1
ASKER
it doesnt matter to me if it is platform specific, I tried the COLLECT in oracle but that returns a dataset.
ASKER
keyword not found on the = sign erik
ASKER
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
ASKER
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(XMLEL EMENT("V", ',' || account)),
'/V/text()'
),
','
) AS STRING
FROM billaccts
GROUP BY billrun;
SELECT billrun,
LTRIM(EXTRACT(XMLAGG(XMLEL
'/V/text()'
),
','
) AS STRING
FROM billaccts
GROUP BY billrun;
ASKER
STRAGG is not being recognized in my query
SELECT
BILLRUN, STRAGG(CUS_PARENT)
FROM BILLRUN.RPT_CUSTOM_XLS
SELECT
BILLRUN, STRAGG(CUS_PARENT)
FROM BILLRUN.RPT_CUSTOM_XLS
ASKER
SELECT billrun,
LTRIM(EXTRACT(XMLAGG(XMLEL EMENT("V", ',' || CUS_PARENT)),
'/V/text()'
),
','
) AS STRING
FROM RPT_CUSTOM_XLS
GROUP BY billrun;
CHARACTER STRING BUFFER TO SMALL ERROR
LTRIM(EXTRACT(XMLAGG(XMLEL
'/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
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
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
ASKER
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'
(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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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?