Matrix table SQL-CodePudding

I have three tables:

Table tCity
```
(Id int, City nvarchar(50))
```
Table tLocation
```
(Id int, Location nvarchar(50))
```
Table tCityLocation
```
(Id int, CityId int, LocationId int)
```

I would like to generate matrix table like in image below.

If City belongs to location-> in appropriate cell in table, char X will be written down.

I would like to about any sophisticated approach how to reach it. I had issue in similar logic and processed by iteration in cursors with dynamically added column to result set table.

Exists any sophisticated approach instead of cursor and dynamically added column?

Thank you.

CodePudding user response：

It should be something like this:

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;

and here is the data I have used:

-- 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;

The idea is to built a dynamic PIVOT, where the PIVOT columns are the unique countries (in your case locations). If your SQL Server supports STRING_AGG, you can do this in different way:

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

CodePudding user response：

Better way for answer to this question use pivot

--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)

You can to insert the basic data with the following codes


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