SQL Combining Rows

Question

I have a table that has three columns: ID, Date, Group. There are two rows for each unique ID (where Group = 1 or where Group = 2). How can I combine these into a table with one row per ID and the Date values sorted into two columns?

I have:

ID      Date        Group
123456  12/7/2016   1
123456  12/3/2016   2
789654  11/8/2016   1
789654  12/1/2016   2

I want:

ID      Date1       Date2
123456  12/7/2016   12/3/2016
789654  11/8/2016   12/1/2016

This is for Sybase 12, if that matters.

Answer 1

SELECT DISTINCT 
    s.ID,
    s1.[Date] AS Date1,
    s2.[Date] AS Date2
FROM @SomeTable s
LEFT JOIN @SomeTable s1 ON s.ID = s1.ID AND s1.[Group] = 1
LEFT JOIN @SomeTable s2 ON s.ID = s2.ID AND s2.[Group] = 2

or

SELECT DISTINCT
    s.ID,
    (SELECT [Date] FROM @SomeTable s1 WHERE s1.ID = s.ID AND s1.[Group] = 1) AS Date1,
    (SELECT [Date] FROM @SomeTable s2 WHERE s2.ID = s.ID AND s2.[Group] = 2) AS Date2
FROM @SomeTable s

are the first two ways which come to mind. Both presume there is only ever one record for each ID/Group combination.

Answer 2

You could also do it using just two tables in the 'from' clause:

select a.ID, a.Date as Date1, b.Date as Date2
from mytable a, mytable b
where a.ID=B.ID
and a.Group=1
and b.Group=2