Матричная таблица SQL

Это должно быть что-то вроде этого:

DECLARE @DymanimcTSQLSatement NVARCHAR(MAX)
       ,@DynamicColumns NVARCHAR(MAX);

SET @DynamicColumns = STUFF
                      (
                        (
                            SELECT ',' + QUOTENAME([Location])
                            FROM tLocation
                            GROUP BY [Location] 
                            ORDER BY [Location] 
                            FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)')
                        ,1
                        ,1
                        ,''
                     );

SET @DymanimcTSQLSatement = N'
SELECT *
FROM
(
    SELECT L.Location
          ,C.City
          ,1
    FROM tCityLocation CL
    INNER JOIN tLocation L
        ON CL.[LocationId] = L.[id]
    INNER JOIN tCity C
        ON CL.[CityId] = C.[id]
) DS (country, city, value)
PIVOT
(
    MAX([value]) FOR [country] IN (' + @DynamicColumns +')
) PVT;';

EXECUTE sp_executesql @DymanimcTSQLSatement;

и вот данные, которые я использовал:

-- Creating Table tCity
CREATE TABLE tCity (
    Id int,
    City nvarchar(50)
);

-- Populating Table tCity with 10 records
INSERT INTO tCity (Id, City) VALUES (1, 'New York');
INSERT INTO tCity (Id, City) VALUES (2, 'Los Angeles');
INSERT INTO tCity (Id, City) VALUES (3, 'Paris');
INSERT INTO tCity (Id, City) VALUES (4, 'Tokyo');
INSERT INTO tCity (Id, City) VALUES (5, 'Sydney');
INSERT INTO tCity (Id, City) VALUES (6, 'Philadelphia');
INSERT INTO tCity (Id, City) VALUES (7, 'Rio de Janeiro');
INSERT INTO tCity (Id, City) VALUES (8, 'Cape Town');
INSERT INTO tCity (Id, City) VALUES (9, 'Beijing');
INSERT INTO tCity (Id, City) VALUES (10, 'Singapore');

-- Creating Table tLocation
CREATE TABLE tLocation (
    Id int,
    Location nvarchar(50)
);

-- Populating Table tLocation with 10 records
INSERT INTO tLocation (Id, Location) VALUES (1, 'United States');
INSERT INTO tLocation (Id, Location) VALUES (2, 'United States');
INSERT INTO tLocation (Id, Location) VALUES (3, 'France');
INSERT INTO tLocation (Id, Location) VALUES (4, 'Japan');
INSERT INTO tLocation (Id, Location) VALUES (5, 'Australia');
INSERT INTO tLocation (Id, Location) VALUES (6, 'United States');
INSERT INTO tLocation (Id, Location) VALUES (7, 'Brazil');
INSERT INTO tLocation (Id, Location) VALUES (8, 'South Africa');
INSERT INTO tLocation (Id, Location) VALUES (9, 'China');
INSERT INTO tLocation (Id, Location) VALUES (10, 'Singapore');

-- Creating Table tCityLocation
CREATE TABLE tCityLocation (
    Id int,
    CityId int,
    LocationId int
);


INSERT INTO tCityLocation (Id, CityId, LocationId)
SELECT 
    ROW_NUMBER() OVER (ORDER BY tCity.Id), 
    tCity.Id, tLocation.Id
FROM tCity 
JOIN tLocation 
ON tCity.Id = tLocation.Id;

Идея состоит в том, чтобы построить динамический PIVOT, где столбцы PIVOT — это уникальные страны (в вашем случае местоположения). Если ваш SQL Server поддерживает STRING_AGG, вы можете сделать это по-другому:

SELECT @DynamicColumns = STRING_AGG(CAST(QUOTENAME([Location]) AS VARCHAR(MAX)), ',') WITHIN GROUP (ORDER BY [Location])
FROM
(
    SELECT DISTINCT [Location]
    FROM tLocation
) DS ([Location]);

Лучший способ ответить на этот вопрос — использовать стержень

Сначала я получаю список Location of table Location

Во-вторых, я получаю список Расположение таблицы tLocation Для использования в выборе Потому что мне нужно использовать ISNULL для замены нуля пробелом

после использования поворота для получения результата

результат: строки включают список городов, а столбцы включают местоположение

--Get List Columns for Pivot

DECLARE @cols AS NVARCHAR(MAX),@scols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Location)  from tLocation
                 FOR XML PATH(''), TYPE  ).value('.', 'NVARCHAR(MAX)')   ,1,1,'' )
 
--can to use below Code for Replace null with space

select @scols = STUFF((SELECT distinct ',ISNULL(' + QUOTENAME(Location) +','' '') as '+  QUOTENAME(Location)
                 from tLocation  FOR XML PATH(''), TYPE  ).value('.', 'NVARCHAR(MAX)')  ,1,1,'')
 
 set @query = 'SELECT  city ,'+@scols+' from 
             (
                select cl.CityId,c.City,l.Location,''X'' as Value from tCityLocation cl
                inner join tCity c on cl.CityId=c.Id
                inner join tLocation l on cl.LocationId=l.Id
            ) x
            pivot 
            (
               max( value)  for Location in (' + @cols + ')
            ) p 
            order by CityId
            '

execute(@query)

Вы можете вставить основные данные со следующими кодами

create table tCity(Id int, City nvarchar(50))

create table tLocation(Id int, Location nvarchar(50))

create table tCityLocation(Id int, CityId int, LocationId int)

insert into tCity
(id,City)
select 1 as id,'NY' union all select 2 as id,'Prague'
union all select 3 as id,'London' union all select 4 as id,'Tokio'

insert into tLocation (id,Location)
select 1,'USA' union all select 2,'CRezah Rep.' union all select 3,'France' union all select 4,'GB'
union all select 5,'Germany' union all select 6,'Europe' union all select 7,'N.America'
union all select 9,'Asia' union all select 10,'Japan'

insert into tCityLocation
(id,CityId,LocationId)
select 1,1,1 union all select 2,1,7 union all select 3,2,2 union all select 4,2,6 union all select 5,3,4 union all
select 6,3,6 union all select 7,4,9 union all select 8,4,10