3
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ What if an employee has more than one child? (or more than one spouse?) \$\endgroup\$ – 200_success Mar 29 '16 at 23:14
  • \$\begingroup\$ In this instance, that is irrelevant. The amount will stay the same. \$\endgroup\$ – Jeff.Clark Mar 29 '16 at 23:32
  • \$\begingroup\$ I would expect two rows in the output for Pete, if the child amounts are different? \$\endgroup\$ – 200_success Mar 30 '16 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 '16 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\$ – 200_success Mar 30 '16 at 4:42
2
\$\begingroup\$

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.

|improve this answer|||||
\$\endgroup\$
  • 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 '16 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 '16 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\$ – 200_success Mar 30 '16 at 20:59
3
\$\begingroup\$

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.

|improve this answer|||||
\$\endgroup\$
  • \$\begingroup\$ Going to look into this as an alternate soon. Thanks! \$\endgroup\$ – Jeff.Clark Mar 30 '16 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 '16 at 16:30
  • \$\begingroup\$ Is the PIVOT perhaps more robust than I need in this situation? \$\endgroup\$ – Jeff.Clark Mar 30 '16 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\$ – Mincong Huang Mar 30 '16 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\$ – 200_success Mar 30 '16 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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