TSQL query to gather amounts for an employees, spouses, and children in one row

Question

I have a query that works as intended, kind of pulling from multiple records in a table, and flattening the data into one row. I am wondering if there is a faster/more semantically correct way to do this than multiple derived tables?

ID      Name      Relationship      Amount
-------------------------------------------
1       James     Employee           2000
1       James     Spouse             5000
1       James     Child             10000
2       Mary      Employee           2000
3       Pete      Employee           2000
3       Pete      Child             10000
3       Pete      Child             10000

And turning it into:

ID      Name      EmployeeAmt      SpouseAmt      ChildAmt
----------------------------------------------------------------------------
1       James     2000             5000           10000
2       Mary      2000             NULL           NULL
3       Pete      2000             NULL           10000

Here is the query:

CREATE TABLE #Source
(
   EmpID varchar(2) NULL
   ,Name varchar(20) NULL
   ,Relationship varchar(20) NULL
   ,Amount varchar(10) NULL
)

INSERT INTO #Source
VALUES( '1', 'James', 'Child', 10000)
     ,('1', 'James', 'Spouse', 5000)
     ,('1', 'James', 'EE', 2000)
     ,('2', 'Mary', 'EE', 2000)
     ,('3', 'Pete', 'EE', 2000)
     ,('3', 'Pete', 'Child', 10000)
     ,('3', 'Pete', 'Child', 10000)

SELECT DISTINCT A.EmpID, A.Name, A.EEAmount, B.SpouseAmount, C.ChildAmount
FROM (
          SELECT EmpID, Name, Relationship, Amount AS 'EEAmount'
          FROM #Source
          WHERE Relationship = 'EE'
     ) AS A

     LEFT JOIN

    (
          SELECT EmpID, Name, Relationship, Amount AS 'SpouseAmount'
          FROM #Source
          WHERE Relationship = 'Spouse'
    ) AS B ON A.EmpID = B.EmpID

    LEFT JOIN 

    (
          SELECT EmpID, Name, Relationship, Amount AS 'ChildAmount'
          FROM #Source
          WHERE Relationship = 'Child'
    ) AS C ON A.EmpID = C.EmpID

SELECT * FROM #Source
DROP TABLE #Source

Answer 1

As you've noticed, all of those LEFT JOINs are unwieldy. On top of that, the joins create phantom rows in the result that you have to deduplicate with DISTINCT.

The solution is to treat this as an aggregation problem rather than a join. You want to GROUP BY the employee.

SELECT EmpId
     , Name
     , MAX(CASE WHEN Relationship = 'EE'     THEN Amount END) AS EmployeeAmt
     , MAX(CASE WHEN Relationship = 'Spouse' THEN Amount END) AS SpouseAmt
     , MAX(CASE WHEN Relationship = 'Child'  THEN Amount END) AS ChildAmt
    FROM #Source
    GROUP BY EmpId, Name
    ORDER BY EmpId;

See this demo.

---

I would also like to note that using single-quoted strings as a column alias (Amount AS 'EEAmount') is sloppy. T-SQL identifiers should be unquoted, "doublequoted", or [bracketed]. For ANSI compliance and compatibility, either use double-quotes, or don't quote the alias at all.

Answer 2

You can use T-SQL's operator PIVOT to do this.

SELECT EmpID, Name, [EE], [Spouse], [Child]
FROM
(
    SELECT EmpID, Name, Relationship, CAST(Amount AS INT) AS AmountINT 
    FROM Source
) AS SourceTable
PIVOT
(
    MAX(AmountINT)
    --
    -- Define your target relationships here
    -- EE, Spouse, Child ...
    -- These relationships should be exactly the same as described in
    -- the SELECT statement
    -- 
    FOR Relationship IN ([EE], [Spouse], [Child]) 
) AS PivotTable;

Result

EmpID   Name    EE      Spouse  Child
1       James   2000    5000    10000
2       Mary    2000    NULL    NULL
3       Pete    2000    NULL    10000

You need to cast your Amount into numeric type before doing so.