Dmitry Dmitry - 1 month ago 9
SQL Question

Combine two tables for one output with separation on different columns

Example of initial data:

Project | Status | Emp1 | Emp2 | Emp3 |
--------------------------------------------------------------
Project 1 | Active | 10 | 10 | 20 |
Project 2 | Closed | 10 | 20 | 10 |
Project 1 | Closed | 20 | 10 | 20 |
Project 1 | Active | 20 | 20 | 10 |
Project 2 | Active | 20 | 10 | 20 |


Using SQL Server 2014, I have 2 queries, which I need to combine to get 1 table grouped by "Project" with new columns of "Emp" based on status.

SELECT [Project],
SUM([Emp1]) as [Emp1_Act],
SUM([Emp2]) as [Emp2_Act],
SUM([Emp2]) as [Emp3_Act],
from t where status = 'Active'
SELECT [Project],
SUM([Emp1]) as [Emp1_Clo],
SUM([Emp2]) as [Emp2_Clo],
SUM([Emp2]) as [Emp3_Clo],
from t where status = 'Closed'


Requested data:

Project | Emp1_Act | Emp2_Act | Emp3_Act | Emp1_Clo | Emp2_Clo | Emp3_Clo |
-----------------------------------------------------------------------------
Project 1 | 30 | 30 | 30 | 10 | 20 | 10 |
Project 2 | 20 | 10 | 20 | 20 | 10 | 20 |

Answer

Because you have a fixed number of columns, you should be able to get away with a standard pivot query:

SELECT Project,
       SUM(CASE WHEN Status = 'Active' THEN Emp1 ELSE 0 END) AS Emp1_Act,
       SUM(CASE WHEN Status = 'Active' THEN Emp2 ELSE 0 END) AS Emp2_Act,
       SUM(CASE WHEN Status = 'Active' THEN Emp3 ELSE 0 END) AS Emp3_Act,
       SUM(CASE WHEN Status = 'Closed' THEN Emp1 ELSE 0 END) AS Emp1_Clo,
       SUM(CASE WHEN Status = 'Closed' THEN Emp2 ELSE 0 END) AS Emp2_Clo,
       SUM(CASE WHEN Status = 'Closed' THEN Emp3 ELSE 0 END) AS Emp3_Clo
FROM t
GROUP BY Project