SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE T (idGeo int IDENTITY(1, 1),
                GEO varchar(64),
                PARENTID int);

INSERT INTO T (GEO,
               PARENTID)
VALUES ('EMEA', NULL),
       ('France', 1),
       ('mIDCAPSfRANCE', 2),
       ('Germany', 1),
       ('France exl midcaps', 2),
       ('Amercias', NULL),
       ('US', 6);
7 rows affected

SELECT T.idGeo,
       T.GEO,
       CHOOSE(C.Level,T.GEO,T1.GEO,T2.GEO) AS [GEO Level 1],
       CHOOSE(C.Level-1,T.GEO,T1.GEO,T2.GEO) AS [GEO Level 1],
       CHOOSE(C.Level-2,T.GEO,T1.GEO,T2.GEO) AS [GEO Level 2]
FROM dbo.T
     LEFT JOIN dbo.T T1 ON T.PARENTID = T1.idGeo
     LEFT JOIN dbo.T T2 ON T1.PARENTID = T2.idGeo
     CROSS APPLY ((SELECT COUNT(V.GEO) AS Level
                   FROM (VALUES(T.GEO),(T1.GEO),(T2.GEO))V(GEO))) C;


idGeo
GEO
GEO Level 1
GEO Level 1
GEO Level 2


1
EMEA
EMEA
null
null

2
France
EMEA
France
null

3
mIDCAPSfRANCE
EMEA
France
mIDCAPSfRANCE

4
Germany
EMEA
Germany
null

5
France exl midcaps
EMEA
France
France exl midcaps

6
Amercias
Amercias
null
null

7
US
Amercias
US
null


Warning: Null value is eliminated by an aggregate or other SET operation.


DROP TABLE dbo.T;

idGeo	GEO	GEO Level 1	GEO Level 1	GEO Level 2
1	EMEA	EMEA	null	null
2	France	EMEA	France	null
3	mIDCAPSfRANCE	EMEA	France	mIDCAPSfRANCE
4	Germany	EMEA	Germany	null
5	France exl midcaps	EMEA	France	France exl midcaps
6	Amercias	Amercias	null	null
7	US	Amercias	US	null