SQL Server 2022

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

CREATE TABLE cities (city varchar(50));

INSERT INTO cities
VALUES  ('Los Angeles')
    ,   ('Ottawa')
    ,   ('San Francisco')
    ,   ('Salt Lake City')
;
4 rows affected

SELECT  cities.city
    ,   s.ordinal
    ,   s.value
FROM    cities
CROSS APPLY 
        string_split(city, ' ', 1) s
;


city
ordinal
value


Los Angeles
1
Los

Los Angeles
2
Angeles

Ottawa
1
Ottawa

San Francisco
1
San

San Francisco
2
Francisco

Salt Lake City
1
Salt

Salt Lake City
2
Lake

Salt Lake City
3
City


WITH split_cities AS
(
    SELECT  cities.city
        ,   s.ordinal
        ,   s.value
    FROM    cities
    CROSS APPLY 
            string_split(city, ' ', 1) s
)
SELECT  *
FROM    split_cities
PIVOT 
    (
        MAX(value)
        FOR ordinal IN ([1], [2], [3], [4], [5])
    ) c
;


city
1
2
3
4
5


Los Angeles
Los
Angeles
null
null
null

Ottawa
Ottawa
null
null
null
null

Salt Lake City
Salt
Lake
City
null
null

San Francisco
San
Francisco
null
null
null

city	ordinal	value
Los Angeles	1	Los
Los Angeles	2	Angeles
Ottawa	1	Ottawa
San Francisco	1	San
San Francisco	2	Francisco
Salt Lake City	1	Salt
Salt Lake City	2	Lake
Salt Lake City	3	City

city	1	2	3	4	5
Los Angeles	Los	Angeles	null	null	null
Ottawa	Ottawa	null	null	null	null
Salt Lake City	Salt	Lake	City	null	null
San Francisco	San	Francisco	null	null	null