SQL Query - Picking Dance partner (Scenario)

 1.   Picking Dance Partners

Pick the dance partners from the following table:

Provide the SQL statement that matches each student Id with an individual of the opposite gender.

Note: There is mismatch in the number of students, as one male student will be left without a dance partner. Please include this individual in your list as well.

Expected Output:

Source Table:

SQL Query:

Script:

with sample as (

select 1001 studentId, 'M' Gender from dual

union

select 2002 studentId, 'M' Gender from dual

union

select 3003 studentId, 'M' Gender from dual

union

select 4004 studentId, 'M' Gender from dual

union

select 5005 studentId, 'M' Gender from dual

union

select 6006 studentId, 'F' Gender from dual

union

select 7007 studentId, 'F' Gender from dual

union

select 8008 studentId, 'F' Gender from dual

union

select 9009 studentId, 'F' Gender from dual

)

SELECT

    male_partner,

    female_partner

FROM

    (

        SELECT

            studentid,

            gender,

            DENSE_RANK() OVER(

                PARTITION BY gender

                ORDER BY

                    studentid

            ) rnk

        FROM

            sample

    ) PIVOT (

        MIN ( studentid )

        FOR ( gender )

        IN ( 'M' AS male_partner, 'F' AS female_partner )

    )

ORDER BY 1;