How to make a query to list a cross match chart

Question

Here are the tables and requested list;

Table A

ID  Description Code
1   Desc1       CodeA
2   Desc2       CodeB
3   Desc3       CodeC
4   Desc4       CodeD
5   Desc5       CodeE

Table B (Relation match between Codes, including them-selves)

ID  TableA_ID   TableA_ID_Relation
1     1                1
2     1                2
3     2                1
4     2                2
5     2                3
6     2                4
7     3                2
8     3                3
9     4                1
10    4                3
11    4                4
12    5                1
13    5                2
14    5                3
15    5                4
16    5                5

Requested list out of the tables A and B above (Relations should be showed in BIT types)

ID  Description         CodeA   CodeB   CodeC   CodeD   CodeE
1   Desc1        CodeA    1       1       0       0       0
2   Desc2        CodeB    1       1       1       1       0
3   Desc3        CodeC    0       1       1       0       0
4   Desc4        CodeD    1       0       1       1       0
5   Desc5        CodeE    1       1       1       1       1

I have started with Pivot queries. But, I couldn't get good results yet. This is the entire structure and the query and the output.

Table Structures:

CREATE TABLE TableA (
    [ID] INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
    , [Description] NVARCHAR(50) NULL
    , [Code] NVARCHAR(10) NULL)

CREATE TABLE TableB (
    [ID] INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
    , [TableA_ID] INT NULL
    , [TableA_ID_Relation] INT NULL)

INSERT INTO TableA([Description], [Code])
VALUES('Desc1','CodeA')
,('Desc2','CodeB')
,('Desc3','CodeC')
,('Desc4','CodeD')
,('Desc5','CodeE')

INSERT INTO TableB([TableA_ID], [TableA_ID_Relation])
VALUES(1,1)
,(1,2)
,(2,1)
,(2,2)
,(2,3)
,(2,4)
,(3,2)
,(3,3)
,(4,1)
,(4,3)
,(4,4)
,(1,1)
,(2,2)
,(3,3)
,(4,4)
,(5,5)

Query:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(Code) 
                    FROM TableA 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')
PRINT @cols
set @query = 'SELECT ID,Description, ' + @cols + ' from 
             (
                  SELECT A.ID, A.Description, A.Code, B.TableA_ID_Relation
                  FROM TableA A
                  LEFT OUTER JOIN TableB B ON B.TableA_ID_Relation = A.ID
            ) x
            pivot 
            (
                MAX(Code)
                for Code in (' + @cols + ')
            ) p '

PRINT @query
execute(@query);

And the result although it is not what I wanted;

So far, I have tried to make a single query and it didn't go well. Before I go for a sort of complex development on that, I wanted to hear your possible opinions.

Answer 1

P.s.
The sample set is just a subset of the example given

---

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

select @cols = STUFF((SELECT ',' + QUOTENAME(Code) 
                    FROM TableA 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

select @cols_isnull = STUFF((SELECT ',isnull(' + QUOTENAME(Code) + ',0) as ' + QUOTENAME(Code)  
                    FROM TableA 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

PRINT @cols
set @query = 'SELECT id,description,code,' + @cols_isnull + ' from
             (
                  SELECT a1.id,a1.code,a1.Description,a2.Code as code2,1 as  indication
                  FROM          TableB b
                        join    TableA a1 
                        on      a1.id = b.TableA_ID
                        join    TableA a2
                        on      a2.id = b.TableA_ID_Relation
            ) x
            pivot 
            (
                MAX(indication)
                for Code2 in (' + @cols + ')
            ) p 
'

PRINT @query
execute(@query);