Separate merged SQL rows with a comma

Issue

I’m having a bit of trouble getting my retrieved values from an SQL query into the correct format.

I’ve managed to join multiple rows into the one value, however I am not sure how to make it separate each of the values with a comma. Essentially I need all the ID’s of a product to be retrieved as, for example, if the database had values of ‘5,6,9,1’ ‘1,3,4’ and ‘2,1’ I want it to throw a comma in between each like -> ‘5,6,9,1,1,3,4,2,1’ instead is doing something more like -> ‘5,6,911,3,42,1’ which is what it is doing at the moment.

The code I’m using is below. Any help would be greatly appreciated.

$hist = "SELECT ORDITEMS FROM cust_orderc WHERE ORDDATE > 
to_date('".$olddate."','dd/mm/yyyy')";


$histitem = OCIParse($db, $hist);
OCIExecute($histitem);

while($row = oci_fetch_array($histitem)){
    $pastitem .= $row['ORDITEMS'];  
}

echo "$pastitem";

Solution

You can do same in oracle using LISTAGG

$hist = "SELECT LISTAGG(ORDITEMS) as ORDITEMS FROM cust_orderc WHERE ORDDATE > to_date('".$olddate."','dd/mm/yyyy')";

Edit OR PHP way

$pastitem = '';
while($row = oci_fetch_array($histitem)){
    $pastitem .= $row['ORDITEMS'] . ',';  
}
$pastitem = trim($pastitem, ",");
echo $pastitem;

Answered By – Yogesh Suthar

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published