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
