Join Tables for Tableau

Question

I needed to create an extract for a Tableau report, where my team developed the database and the web system and another company developed the Table report.

This is the scheme of the problem

To feed the Tableau report I developed the solution below, but I don’t know if this was the best solution

DECLARE @Pillars table (Id int , Name varchar(30), ExhibitionOrder tinyint)
DECLARE @Initiatives table (Id int identity(100,2) , PillarId int,  Name varchar(35), ExhibitionOrder tinyint) 
DECLARE @Goals table (Id int identity(10,30) , PillarId int, Descriptions varchar(60), ExhibitionOrder tinyint) 

INSERT @Pillars (Id, Name, ExhibitionOrder) values 
 (1, 'Communication',1)
,(2, 'Environmental Responsibility',2)


INSERT @Initiatives (PillarId, Name, ExhibitionOrder) Values
(1,'Social Network',2)
,(1,'Television',1)
,(1,'Brand Content',3)
,(2,'Financial education',1)
,(2,'Your garbage, Your responsibility',0)

INSERT @Goals (PillarId, Descriptions , ExhibitionOrder) Values
 (1, 'Improves the number of direct contact on call center in 10%',2)
,(1, 'Reduces paper media in 2%',1)
,(2, 'Reduces in 35% the recycle garbage in the office',1)
,(2, 'Reduces in 2% the carbon footprint on the office',1)
,(2, 'Reduced 2% Hydro bill',1)


;WITH 
lv0(n) AS (SELECT 0 FROM   (VALUES (0), (0))G(n)), --2 
lv1(n) AS (SELECT 0 FROM   lv0 a CROSS JOIN lv0 b), -- 4 
lv2(n) AS (SELECT 0 FROM   lv1 a CROSS JOIN lv1 b), -- 16 
lv3(n) AS (SELECT 0 FROM   lv2 a CROSS JOIN lv2 b), -- 256 
lv4(n) AS (SELECT 0 FROM   lv3 a CROSS JOIN lv3 b), -- 65,536 
--lv5(N) as (select 0 from lv4  a cross join lv4 b), -- 4,294,967,296 
tally(n) AS (SELECT Row_number() OVER( ORDER BY (SELECT NULL)) FROM   lv4), 
t1 AS (SELECT Row_number() OVER( ORDER BY id ) N ,* FROM   @Pillars), 
t2 AS (SELECT Row_number() OVER( ORDER BY id ) N ,* FROM   @Initiatives), 
t3 AS (SELECT Row_number() OVER( ORDER BY id ) N ,* FROM   @Goals) 
SELECT  A.Name Pilar 
       ,b.Name Initiative 
       ,c.Descriptions Goals
       ,a.id 
       ,b.PillarId 
       ,c.PillarId 
FROM   tally t 
       CROSS JOIN t1 a 
       LEFT JOIN t2 b 
              ON t.n = b.n 
                 AND a.id = b.PillarId 
       LEFT JOIN t3 c 
              ON t.n = C.n 
                 AND a.id = c.PillarId 
WHERE  a.id IS NOT NULL 
       AND ( b.id IS NOT NULL 
              OR c.id IS NOT NULL ) 
ORDER  BY  IsNUll(a.ExhibitionOrder,255)
          ,IsNUll(b.ExhibitionOrder,255) 
          ,IsNUll(c.ExhibitionOrder,255)

            SELECT 
                    A.Name Pilar 
                   ,b.Name Initiative 
                   ,c.Descriptions Goals
                   ,a.id 
                   ,b.PillarId 
                   ,c.PillarId                 
            FROM
                @Pillars A
            INNER JOIN @Initiatives B
                ON
                A.Id = B.PillarId
            INNER JOIN @Goals C
                ON  
                A.Id = B.PillarId
            ORDER  BY  IsNUll(a.ExhibitionOrder,255)
                      ,IsNUll(b.ExhibitionOrder,255) 
                      ,IsNUll(c.ExhibitionOrder,255)

Upshot

Pilar                          Initiative                          Goals                                                        id          PillarId    PillarId
------------------------------ ----------------------------------- ------------------------------------------------------------ ----------- ----------- -----------
Communication                  Television                          Reduces paper media in 2%                                    1           1           1
Communication                  Social Network                      Improves the number of direct contact on call center in 10%  1           1           1
Communication                  Brand Content                       NULL                                                         1           1           NULL
Environmental Responsibility   Your garbage, Your responsibility   Reduced 2% Hydro bill                                        2           2           2
Environmental Responsibility   Financial education                 Reduces in 2% the carbon footprint on the office             2           2           2
Environmental Responsibility   NULL                                Reduces in 35% the recycle garbage in the office             2           NULL        2

(6 row(s) affected)

Answer 1

For the 3 tables there is the connection field Pillarid. That is, a hierarchy in 3 levels. But the field of connection between the 3 levels is the same. Then the result will be that same, many rows, as the result will be to return all records from table 3 for each Pillarid from table 2 for each ID from table 1. In this case, for id 1 from table 1 there are 3 records in table 2 and 2 records in table 3, this will result in, 2 records (table 3) for each record in table 2.

To avoid this, it is necessary to have a another connection field between tables 2 and 3.

Answer 2

It seems to me that the query can be mounted with the use of FULL OUTER JOIN type junction between the Initiatives and Goals tables, adding sequencing per pillar in each of these two tables.

-- código #1 v4
with 
cte_I as (
SELECT *, 
       Seq= row_number() over (partition by PillarId 
                               order by coalesce(ExhibitionOrder, Id))
  from @Initiatives
),  
cte_G as (          
SELECT *, 
       Seq= row_number() over (partition by PillarId 
                               order by coalesce(ExhibitionOrder, Id))
  from @Goals
)
SELECT coalesce(I.PillarId, G.PillarId) as PillarId,
       P.Name as Pilar,
       I.Name as Iniciativa, 
       G.Descriptions as Objetivo
  from cte_I as I 
       full outer join cte_G as G on I.PillarId = G.PillarId
                                     and I.Seq = G.Seq
       inner join @Pillars as P on P.Id = coalesce(I.PillarId, G.PillarId)
  order by P.ExhibitionOrder, coalesce(I.Seq, 255), coalesce(G.Seq, 255);

Pillar sequencing is reliable in cases where the Exhibitionorder column is either informed for all rows or for no row of the same pillar. But, if for a given Pillarid value there is information of Exhibitionorder in only part of the lines, the sequencing by pillar can (or not) generate unexpected order.