Sql server join by group?

Question

I have this table :

id   |  type |      date    
 1   |    a  |   01/1/2012   
 2   |    b  |   01/1/2012
 3   |    b  |   01/2/2012   
 4   |    b  |   01/3/2012   
 5   |    a  |   01/5/2012   
 6   |    b  |   01/5/2012   
 7   |    b  |   01/9/2012   
 8   |    a  |   01/10/2012   

The POV is per date. if 2 rows contains the same date , so both will visible in the same line ( left join).

Same date can be shared by 2 rows max.

so this situation can't be :

 1   |    a  |   01/1/2012   
 2   |    b  |   01/1/2012
 3   |    a  |   01/1/2012

if in the same date there is group a and b show both of them in single line using left join

if in date there is only a group , show it as single line ( +null at the right side )

if in date there is only b group , show it as single line ( +null at the left side )

Desired result :

   Date         |typeA|typeB  |a'id|b'id
  01/1/2012     |  a  |  b    | 1  |  2
  01/2/2012     |     |  b    |    |  3
  01/3/2012     |     |  b    |    |  4
  01/5/2012     |   a |  b    | 5  |  6
  01/9/2012     |     |  b    |    |  7
  01/10/2012    |   a |       | 8  |  

I know this suppose to be simple , but the main anchor of join here is the date. The problem I've encountered is when I read line 1 , i search in the table all rows with the same date...fine. - its ok.

But when I read the second line , I do it also , and it yields the first row - which already was counted...

any help ?

here is the sql fiddle :

https://data.stackexchange.com/stackoverflow/query/edit/82605

Answer 1

I think you want a pivot

 select 
    [date],
    case when [a] IS null then null else 'a' end typea,
    case when [b] IS null then null else 'b' end typeb,
    a as aid,
    b as bid
 from yourtable src
 pivot  (max(id) for type in ([a],[b]))p 

If you want to do it with joins..

select ISNULL(a.date, b.date), a.type,b.type, a.id,b.id
from
(select * from yourtable where type='a') a
    full outer join
(select * from yourtable where type='b') b  
    on a.date = b.date