Transpose column result in resultset to rows

i have a resultset like this

Continent   Country
------- -------
Asia    Japan
Asia    Russia
Asia    India
Europe  Britain
Europe  France

from query

select continent,country from tablexxx

i want result in the format

Continent   Country
------- -------
Asia    Japan,Russia,India
Europe  Britain,France

I have heard of pivot tables. but it seems difficult to me... any help with the query please :)

Here's my final solution in SQL Server , it wo开发者_开发百科rks...:)

SELECT     continents, Countries = replace
                          ((SELECT Countries AS [data()]
                              FROM tblXXX
                              WHERE  continents = a.continents
                              ORDER BY continents FOR xml path('')), ' ',  ',' )
FROM       tblXXXa
WHERE     continents IS NOT NULL
GROUP BY continents

If you use MySQL you need to use GROUP_CONCAT

Example:

SELECT continent, GROUP_CONCAT(county ORDER BY country) as Countries 
FROM tablexxx
GROUP BY continent
ORDER BY continent

Link
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

You can use the COALESCE function to build a comma-delimited list from rows, like in this example:

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList