group by query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

group by query

Author

Topic

actuary
Starting Member

23 Posts

Posted - 2006-10-11 : 06:13:50

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!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-11 : 06:24:03

[code]-- Prepare test data
DECLARE @Test TABLE (Rank TINYINT, Name VARCHAR(10), Name_Id SMALLINT, isMale BIT)

INSERT @Test
SELECT 1, 'Jacob', 20288, 1 UNION ALL
SELECT 1, 'Joshua', 19962, 1 UNION ALL
SELECT 2, 'Matthew', 18907, 1 UNION ALL
SELECT 2, 'Michael', 24096, 1 UNION ALL
SELECT 3, 'Daniel', 21708, 1 UNION ALL
SELECT 3, 'David', 21838, 1 UNION ALL
SELECT 4, 'Faith', 29717, 0 UNION ALL
SELECT 4, 'Hope', 28258, 0

-- Do the work
SELECT d.Rank,
t1.Name Name_1,
t1.Name_Id Name_Id_1,
t1.isMale isMale_1,
t2.Name Name_2,
t2.Name_Id Name_Id_2,
t2.isMale isMale_2
FROM (
SELECT Rank,
MIN(Name_Id) minid,
MAX(Name_Id) maxid
FROM @Test
GROUP BY Rank
) d
INNER JOIN @Test t1 ON d.minid = t1.Name_Id
INNER JOIN @Test t2 ON d.maxid = t2.Name_Id[/code]

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-11 : 10:18:11

Where do you want to show data?
If there are thousands of Names for single name, do you want all in columns?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources