Transpose column result in resultset to rows

Question

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 works...:)

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

for mysql see this question http://stackoverflow.com/questions/4561650/mysql-group-concat-duplicates — sll, Aug 11 '11 at 13:55

score 1 · Accepted Answer · answered Aug 11 '11 at 13:56

1

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

answered Aug 11 '11 at 13:56

Johan

74,508
24
191
319

thanx a ton...found my answer here http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 – sajad Aug 11 '11 at 14:01

score 0 · Answer 2 · answered Aug 11 '11 at 13:55

0

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

answered Aug 11 '11 at 13:55

James Johnson

45,496
8
73
110

This will not work, except in as a building block in procedural code, is really not SQL however. – Johan Aug 11 '11 at 13:57
i wanted to write a single query for this, without use of any variables – sajad Aug 11 '11 at 13:59
You can put the concept into a UDF. That's what I did. If the suggestion from @Johan works though, that might be better. – James Johnson Aug 11 '11 at 14:01

Transpose column result in resultset to rows

2 Answers2