I have a table with single column Countries as:
Countries
USA
USA
France
India
USA
Russia
France
India
I want to concatenate distinct values in a single row like
Countries
USA,France,India,Russia
How do I write SQL query to achieve the same?
Thanks in advance.
CodePudding user response:
If its from a table Country and fieldName is CountryName, then below query would do good, SELECT STRING_AGG(CountryName) FROM (SELECT DISTINCT CountryName FROM Country)
. Incase if you are using older version of SQL, STUFF can be used to achieve the output.
CodePudding user response:
WITH CTE(COUNTRY)AS
(
SELECT 'USA' UNION ALL
SELECT'USA'UNION ALL
SELECT'France'UNION ALL
SELECT'India'UNION ALL
SELECT'USA'UNION ALL
SELECT'Russia'UNION ALL
SELECT'France'UNION ALL
SELECT'India'
)
SELECT STRING_AGG(C.COUNTRY,',')
FROM
(
SELECT DISTINCT COUNTRY FROM CTE
)C