I have three columns,
COUNTRY. One, two, or all columns may be NULL.
With the following logic, a single string is made using the SQL below. It is messy, and I am wondering if there is a simpler way to achieve the output? I've considered CONCAT() but I don't think I can use that and still get the separators in there.
- if both CITY and STATE have a value, use that, separated by
', '. Exclude COUNTRY.
- if only one of CITY or STATE exists, AND Country exists, use the one of CITY/STATE which exists, plus the separator, and COUNTRY.
- if neither CITY or STATE exist, use country only with no separator
- if only one of the three exists, use it, as is, with no separator
- If none exist, output should be NULL (not
I will add that if a CITY and/or STATE value exists, then COUNTRY will never be NULL.
Here is the code so far. It is messy but I believe it meets the output requirements listed above.
I am working on SQL Server 2008.
CASE WHEN CASE WHEN ISNULL(M.CITY, '') = '' THEN '' ELSE ISNULL(M.CITY, '') + ', ' END + CASE WHEN ISNULL(M.PROVINCE, '') = '' THEN '' ELSE ISNULL(M.PROVINCE, '') END = '' THEN CASE WHEN COUNTRY IS NOT NULL THEN COUNTRY ELSE NULL END ELSE CASE WHEN ISNULL(M.CITY, '') = '' THEN '' ELSE ISNULL(M.CITY, '') + ', ' END + CASE WHEN ISNULL(M.PROVINCE, '') = '' THEN '' ELSE ISNULL(M.PROVINCE, '') END END + CASE WHEN (M.CITY IS NULL AND M.PROVINCE IS NOT NULL) OR (M.CITY IS NOT NULL AND M.PROVINCE IS NULL) THEN CASE WHEN PROVINCE IS NOT NULL AND CITY IS NULL THEN ', ' ELSE '' END + COUNTRY ELSE '' END LOCATION