MySQL & MSSQL Tutorial: Combine Multiple Rows Into A Single, Comma Delineated List

In MySQL with PHP:

<?
$delimiter = ", ";
$count = 0;

$loop = mysql_query("SELECT S.suffix FROM people P
          INNER JOIN surnames S on P.suffix_id = S.id")
          or die ('cannot select merged data');

while ($row = mysql_fetch_array($loop))
{
    $merged_data .= $row['suffix'];

    if ($count+1 < mysql_num_rows($loop))
       $merged_data .= $delimiter;

    $count++;
}

echo $merged_data;
?>

In MSSQL:

declare @merged_data varchar(2000), @delimiter char(1)  --declare your variable

set @delimiter = ', ' --whatever you want to separate the field values

--join two tables and select the fields you want to merge into a single field
 select @merged_data = isNull(@merged_data + @delimiter, '') + S.suffix
 from people P inner join surnames S on P.suffix_id = S.id

 select @merged_data

You may also like...

Leave a Reply