Create pivot table from 3 tables using SQL-CodePudding

I am trying to join three tables to then create a pivot table. I'm struggling to find figure out how to do this.

Below is an example of the format of the three existing tables and the desired out output table.

I am working in Tableau and the data is feeding in from a Microsoft SQL server.

Contact Table (Table 1)

Contact ID	Name	Channel	Contact Type	Stage 1	Stage 2
12098	Andrew	Google	Client	44593	44597
34556	Joe	Facebook	Client	44568	44576
32344	Paul	Indeed	Buyer
32211	Simon	Indeed	Client	44562	44570
21212	Peter	Facebook	Client	44599	44601
29871	Sam	Google	Client	44611	44613

Contact/Deal Association (Table 2)

Deal ID	Contact ID
543	12098
232	34556
456	32211
787	21212
654	29871

Deal History (Table 3)

Deal ID	Stage 3	Stage 4	Stage 5
543	44599	44601
232	44582	44593	44599
456	44580
787	44610	44612	44615
654	44615

Pivot Table (Output Table)

Contact ID	Pivot Name	Pivot Value
12098	Stage 1	44593
12098	Stage 2	44597
12098	Stage 3	44599
12098	Stage 4	44601
34556	Stage 1	44568
34556	Stage 2	44576
34556	Stage 3	44582
34556	Stage 4	44593
34556	Stage 5	44599
32211	Stage 1	44562
32211	Stage 2	44570
32211	Stage 3	44580
21212	Stage 1	44599
21212	Stage 2	44601
21212	Stage 3	44610
21212	Stage 4	44612
21212	Stage 5	44615
29871	Stage 1	44611
29871	Stage 2	44613
29871	Stage 3	44615

Any advise would be greatly appreciated.

Thank you.

CodePudding user response：

Maybe not the best solution , but this might work for you:

select ContactID,
       PivotName,
       PivotValue                 
FROM (
       select ContactID,max(Stage1) as Stage1,max(Stage2) as Stage2,max(Stage3) as Stage3,max(Stage4) as Stage4,max(Stage5) as Stage5
          from (
                  select cd.ContactID,c.Stage1,null as Stage2,null as Stage3,null as Stage4,null as Stage5
                  from  Contact_deal cd inner join DealHistory dh on dh.DealID=cd.DealID inner join Contact c on c.ContactID=cd.ContactID
                  union all
                  select cd.ContactID,null as Stage1,c.Stage2,null as Stage3,null as Stage4,null as Stage5
                  from  Contact_deal cd inner join DealHistory dh on dh.DealID=cd.DealID inner join Contact c on c.ContactID=cd.ContactID
                  union all
                  select cd.ContactID,null as Stage1,null as Stage2,dh.Stage3,null as Stage4,null as Stage5
                  from  Contact_deal cd inner join DealHistory dh on dh.DealID=cd.DealID inner join Contact c on c.ContactID=cd.ContactID
                  union all
                  select cd.ContactID,null as Stage1,null as Stage2,null as Stage3,dh.Stage4,null as Stage5
                  from  Contact_deal cd inner join DealHistory dh on dh.DealID=cd.DealID inner join Contact c on c.ContactID=cd.ContactID
                  union all
                  select cd.ContactID,c.Stage1,null as Stage2,null as Stage3,null as Stage4,dh.Stage5
                  from  Contact_deal cd inner join DealHistory dh on dh.DealID=cd.DealID inner join Contact c on c.ContactID=cd.ContactID 
                 )  t1 group by ContactID 
      ) t2 
unpivot
(
  PivotValue
  for PivotName in (Stage1,Stage2,Stage3,Stage4,Stage5)
) unpiv;

Result:

ContactID  PivotName  PivotValue
  12098     Stage1     44593
  12098     Stage2     44597
  12098     Stage3     44599
  12098     Stage4     44601
  21212     Stage1     44599
  21212     Stage2     44601
  21212     Stage3     44610
  21212     Stage4     44612
  21212     Stage5     44615
  29871     Stage1     44611
  29871     Stage2     44613
  29871     Stage3     44615
  32211     Stage1     44562
  32211     Stage2     44570
  32211     Stage3     44580
  34556     Stage1     44568
  34556     Stage2     44576
  34556     Stage3     44582
  34556     Stage4     44593
  34556     Stage5     44599

Demo