Search code examples
sqlselectunpivot

There is a table having country and city columns as shown in the below table input. I need the output as mentioned below

enter image description here

**enter image description here**

I need a SQL query to get the desired output from the input table

Solution

  • You can do this with a UNION query, first selecting the distinct country names, and then each of the cities for that country. The output is then ordered by the country; whether the value is a country or a city; and then by the value:

    SELECT DISTINCT country AS data, country, 1 AS ctry
FROM cities
UNION ALL
SELECT city, country, 0
FROM cities
ORDER BY country, ctry DESC, data

    Output:

    data    country     ctry
India   India       1
BNG     India       0
CHN     India       0
HYD     India       0
Sweden  Sweden      1
GOTH    Sweden      0
STOCK   Sweden      0
VAXO    Sweden      0

    Demo on dbfiddle