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

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

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.

StackExchange Code Review Q#124215, answer score: 3