How to retrieve same column twice from one table w

问题:

I am trying to retrieve a column twice from one table for ex:

select M.Event_Name as 'Male',
       F.Event_Name as 'Female' 
from   Table1 M, Table1 F
where  M.Gender = 'M'
       and F.Gender = 'F'
       and F.Country = 12
       and M.Country = 12

Table1 data

ID    Event_Name   Gender  Country
1     Cricket      M       12
2     FootBall     M       13
3     BasketBall   M       12
4     Hockey       M       12
5     Tennis       M       13
6     Volly Ball   M       13
7     Cricket      F       13
8     FootBall     F       13
9     BasketBall   F       12
10    Hockey       F       13
11    Tennis       F       12
12    Volly Ball   F       12

What I Got is :

Male           Female
Cricket        Tennis
Cricket        BasketBall
Cricket        Volly ball
BasketBall     Tennis
BasketBall     BasketBall
BasketBall     Volly ball
Hockey         Tennis
Hockey         BasketBall
Hockey         Volly ball

Expecting:

Male          Female
Cricket       Tennis
BasketBall    BasketBall
Hockey        Volly ball

Help me out.. Thanks

回答1:

You should be able to use something like this which incorporates a PIVOT:

select M as Male, 
  F as Female
from
(
  select event_name, gender,
    row_number() over(partition by gender, country order by id) rn
  from yourtable
  where gender in ('M', 'F')
    and country = 12
) src
pivot
(
  max(event_name)
  for gender in (M, F)
) piv

See SQL Fiddle with Demo

Or you can use an aggregate function with a CASE statement:

select 
  max(case when gender = 'M' then event_name end) male,
  max(case when gender = 'F' then event_name end) female
from
(
  select event_name, gender,
      row_number() over(partition by gender, country order by id) rn
  from yourtable 
  where gender in ('M', 'F')
    and country = 12
) src
group by rn

See SQL Fiddle with Demo

Both produce the same result:

|       MALE |     FEMALE |
---------------------------
|    Cricket | BasketBall |
| BasketBall |     Tennis |
|     Hockey | Volly Ball |