Group query 1

[shams123]

Hi guys,

I have a table with the following data:

Rank Name Name_Id isMale
1 Jacob 20288 1
1 Joshua 19962 1
2 Matthew 18907 1
2 Michael 24096 1
3 Daniel 21708 1
3 David 21838 1
4 Faith 29717 0
4 Hope 28258 0

I want to group the names on Rank and have the query returned as:

Rank Name_1 Name_Id_1 isMale_1 Name_2 Name_Id_2 isMale_2
1 Jacob 20288 1 Joshua 19962 1
2 Mathew 18907 1 Michael 24096 1
3 Daniel 21708 1 David 21838 1
4 Faith 29717 0 Hope 28258 0

Any help would be highly appreciated.

Thanks!

 

[PHV]

A starting point:
SELECT A.Rank
, A.Name Name_1, A.Name_Id Name_Id_1, A.isMale isMale_1
, B.Name Name_2, B.Name_Id Name_Id_2, B.isMale isMale_2
FROM yourTable A
INNER JOIN yourTable B ON A.Rank = B.Rank AND A.Name_Id < B.Name_Id

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shams123]

Hi mate,

We're almost there. What should I do if there are nulls in some name_id columns?

Cheers!

 

[PHV]

Perhaps this ?
INNER JOIN yourTable B ON A.Rank = B.Rank AND A.Name < B.Name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886