samedi 9 juillet 2016

SQL Pivot from multiple tables and columns

I am working in SQL Server (SSMS) with three tables like so

Table A

ProcessID Step Approver  
  17       10    Kim  
  18       20    Joe  
  19       10    Beth  
  20       20    Josh  

Table B

ProcessID DeptCode  
   17       515  
   18       515  
   19       190  
   20       190  

Table C

ProcessID AlternateApprover  
   17        Sarah  
   18        David  
   19        Don  
   20        Don

and would like my results formatted as:

DeptCode FirstApprover FirstAlternate SecondApprover SecondAlternate  
   515       Kim           Sarah          Joe             David  
   190       Beth          Don            Josh            Don

I've managed a pivot on just the approvers successfully, but am not sure how to go about joining the alternates (Table C).

I understand that this normalization is a bit odd - it's not my database and I have no control over the table structure

Aucun commentaire:

Enregistrer un commentaire