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?

do you only have 1 date per date column per name? (or in the case of multiples, will grabbing the top one be sufficient?) — Rohan Büchner
– Rohan Büchner, Commented Jun 22, 2016 at 6:56

Giorgos Betsos · Accepted Answer · 2016-06-22 06:55:29Z

8

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

answered Jun 22, 2016 at 6:55

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

D Mayuri · Accepted Answer · 2016-06-22 07:02:59Z

1

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

answered Jun 22, 2016 at 7:02

D Mayuri

4562 silver badges6 bronze badges

1 Comment

Dr. Stitch Over a year ago

its more complicated than the question but for the effort will give you 1 vote. :)

Collectives™ on Stack Overflow

Grouping in SQL

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related