parent-child relation to flat view

Is there an easy way to migrate a table with a parent child relation to a column one ?

INPUT_TABLE

PARENT_ID,ID,NAME
null,1, USA
1   ,2, Las Vegas
2   ,3, City in las Vegas
2   ,4, Another City in las Vegas
.. a lot more

OUT开发者_StackOverflow社区PUT

ID, COUNTRY, CITY, PLACE
1,  USA, null,null
2,  USA, Las Vegas,null
3,  USA, Las Vegas,City in las Vegas
4,  USA, Las Vegas,Another City in las Vegas

Thanks in advance

---

Provided you have a hierarchie of 3 items as per your comments and there's no need to retain rows that have no City or Place, joining the table twice with itself would suffice.

SQL Statement

SELECT  ID = Country.ID
        , Country = Country.NAME
        , City = City.NAME
        , Place = Place.Name
FROM    q Country
        INNER JOIN q City ON City.PARENT_ID = Country.ID
        INNER JOIN q Place ON Place.PARENT_ID = City.ID

(SQL Server) Test script

;WITH q (PARENT_ID, ID, NAME) AS (
  SELECT null, 1, 'USA'
  UNION ALL SELECT 1, 2, 'Las Vegas'
  UNION ALL SELECT 2, 3, 'City in las Vegas'
  UNION ALL SELECT 2, 4, 'Another City in las Vegas'
)
SELECT  ID = Country.ID
        , Country = Country.NAME
        , City = City.NAME
        , Place = Place.Name
FROM    q Country
        INNER JOIN q City ON City.PARENT_ID = Country.ID
        INNER JOIN q Place ON Place.PARENT_ID = City.ID