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:

   EmpID varchar(2) NULL
   ,Name varchar(20) NULL
   ,Relationship varchar(20) NULL
   ,Amount varchar(10) NULL

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
          SELECT EmpID, Name, Relationship, Amount AS 'EEAmount'
          FROM #Source
          WHERE Relationship = 'EE'
     ) AS A


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


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

  • \$\begingroup\$ What if an employee has more than one child? (or more than one spouse?) \$\endgroup\$ Mar 29, 2016 at 23:14
  • \$\begingroup\$ In this instance, that is irrelevant. The amount will stay the same. \$\endgroup\$
    – Jeff.Clark
    Mar 29, 2016 at 23:32
  • \$\begingroup\$ I would expect two rows in the output for Pete, if the child amounts are different? \$\endgroup\$ Mar 30, 2016 at 0:51
  • \$\begingroup\$ Those amounts are static, they will never change. I should have substituted Amount for something else. Amount--while it truly what will be in the final data--is confusing. Such is life when dealing with insurance. \$\endgroup\$
    – Jeff.Clark
    Mar 30, 2016 at 4:38
  • 1
    \$\begingroup\$ So, the amount for an employee is always 2000, the amount for a spouse is always 5000, and the amount for a child is always 10000, and only the existence (and not the number) of each kind of dependant is relevant? \$\endgroup\$ Mar 30, 2016 at 4:42

2 Answers 2


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.

     , 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.

  • 1
    \$\begingroup\$ Also, thank you for the back and fourth questions on the initial question. That helps me to now see that I need to be every explicit. Little bits of information do help, like being able to use MAX() to filter out duplicates because numbers in Amount are static. Which wouldn't have been known if you hadn't pulled it out of me :) \$\endgroup\$
    – Jeff.Clark
    Mar 30, 2016 at 16:16
  • \$\begingroup\$ 200_success, Mincong's answer below uses the PIVOT, which seems well suited for my situation also. As he mentions he is new to PIVOT tables as well, I am wondering if you could explain the merits of PIVOT vs your solution, if there are any? \$\endgroup\$
    – Jeff.Clark
    Mar 30, 2016 at 20:26
  • 1
    \$\begingroup\$ PIVOT is less portable: it works in SQL Server and Oracle, but is not specified in ANSI SQL. PostgreSQL does it completely differently, using crosstab(), and MySQL doesn't have anything like it. Conceptually, PIVOT is awkward in that it blurs the lines between string data and column identifiers. On the other hand, PIVOT is arguably a closer description of what the query aims to achieve (classifying the data of one column into multiple columns). \$\endgroup\$ Mar 30, 2016 at 20:59

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

SELECT EmpID, Name, [EE], [Spouse], [Child]
    SELECT EmpID, Name, Relationship, CAST(Amount AS INT) AS AmountINT 
    FROM Source
) AS SourceTable
    -- 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;


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.

  • \$\begingroup\$ Going to look into this as an alternate soon. Thanks! \$\endgroup\$
    – Jeff.Clark
    Mar 30, 2016 at 15:51
  • \$\begingroup\$ So, my situation seems to be what the PIVOT was made for. What are the merits to using PIVOT vs 200_Success's answer above? \$\endgroup\$
    – Jeff.Clark
    Mar 30, 2016 at 16:30
  • \$\begingroup\$ Is the PIVOT perhaps more robust than I need in this situation? \$\endgroup\$
    – Jeff.Clark
    Mar 30, 2016 at 17:29
  • \$\begingroup\$ Actually, I'm new to PIVOT as well. I'll study more about this operator and reply you when I return to company tomorrow. @Jeff.Clark \$\endgroup\$ Mar 30, 2016 at 18:38
  • \$\begingroup\$ This actually gives the wrong result for Pete's children. The desired ChildAmt is 10000 if Pete has any children; he doesn't get another 10000 for the second child. \$\endgroup\$ Mar 30, 2016 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.