Grouping in SQL

Question

I have no idea at the moment how I can write this SQL statement for SQL Server.

I have 2 tables:

Customer:

ID | Name   | Prename
----------------------
1    Miller   Thomas
....

Steps:

ID | FK_customer | step | created
------------------------------------
1    1             A      2010-02-03
2    1             B      2011-09-12
....

If I try to join it, I get this:

Name   | A_date     | B_date
----------------------------------
Miller   2010-02-03   NULL
Miller   NULL         2011-09-12

What I want is this:

Name   | A_date     | B_date
---------------------------------
Miller   2010-02-03   2011-09-12

Can anybody show me the light?

Answer 1

You can use conditional aggregation for this:

SELECT c.Name,
       MAX(CASE WHEN step = 'A' THEN created END) AS A_date,
       MAX(CASE WHEN step = 'B' THEN created END) AS B_date
FROM customer c
JOIN steps s ON c.Id = s.FK_customer
GROUP BY c.Name   

Answer 2

SELECT c.Name, A.A_date, B.B_date
FROM Customer c
CROSS APPLY
(
    SELECT MAX(created) AS A_date
    FROM steps s
    WHERE s.FK_customer  = c.Id
    AND s.step = 'A'
)AS A
CROSS APPLY
(
    SELECT MAX(created) AS B_date
    FROM steps s
    WHERE s.FK_customer  = c.Id
    AND s.step = 'B'
)B