Saturday, July 4, 2009

List all countries against their region separated with comma.

On my free time i found one table which list countries with their region like(Figure-1),they were using simple string base query to put this data as Shown in (Figure-2).

Figure-1(Data)

Figure-2(Result)


All in above query is to List all country against their region separated with comma.
this is possible in many ways. One i found by googling is on http://www.simple-talk.com/sql/t-sql-programming/sql-server-grouping-workbench/
And another i will present is written by me and my friend prashant meghvani find out another using new XML Datatype is.
This is the Test Data:
you can use to test this method.just copy and paste it in your Query editor.

DECLARE @t TABLE
(
id INT,
country VARCHAR(30),
barrels INT,
region VARCHAR(30)
)
INSERT INTO @t
(
id,
country,
barrels,
region
)
SELECT 1,
'Australia',
541000,
'Australia and New Zealand'
UNION
SELECT 2,
'New Zealand',
10000,
'Australia and New Zealand'
UNION
SELECT 3,
'Cuba',
73500,
'Caribbean'
UNION
SELECT 3,
'Trinidad and Tobago',
15240,
'Caribbean'
UNION
SELECT 4,
'Virgin Islands',
11480,
'Caribbean'
UNION
SELECT 5,
'Belize',
14000,
'Central America'
UNION
SELECT 6,
'Mexico',
541000,
'Central America'
UNION
SELECT 7,
'Madagascar',
756000,
'Eastern Africa'
UNION
SELECT 8,
'Mongolia',
541000,
'Eastern Africa'
UNION
SELECT 9,
'Hungary',
541000,
'Eastern Europe'
UNION
SELECT 10,
'Poland',
188300,
'Eastern Europe'
UNION
SELECT 11,
'Papua New Guinea',
541000,
'Melanesia'
UNION
SELECT 12,
'Congo',
32900,
'Middle Africa'
UNION
SELECT 13,
'Chad',
1126000,
'Middle Africa'
UNION
SELECT 14,
'Equatorial Guinea',
10000,
'Middle Africa'
UNION
SELECT 15,
'Pakistan',
63000,
'South-central Asia'
UNION
SELECT 16,
'India',
819000,
'South-central Asia'
UNION
SELECT 17,
'Iran',
4081000,
'South-central Asia' ;


Solution:

SELECT * FROM @t
WITH FirstTable ( region, Countries )
AS ( SELECT region,
( SELECT country + ISNULL(NULL, ',')
FROM @t child
WHERE parent.region = child.region
FOR
XML PATH('')
)
FROM @t parent
GROUP BY parent.region
)
SELECT Region,
CASE WHEN LEN(Countries) > 0
THEN LEFT(Countries, LEN(Countries) - 1)
ELSE ''
END Countries
FROM FirstTable


Hope this will helps.
Thanks.