Sener Sener - 18 days ago 6
SQL Question

How to make a query to list a cross match chart

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;

enter image description here

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

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