Can this be sorted without temporary tables

Question

Post reply

Can this be sorted without temporary tables

webskater

SSCarpal Tunnel

Points: 4214
More actions
May 31, 2013 at 3:51 am

#394884

Two tables - one containing (team) Leaders and one containing (team) members
CREATE TABLE #tblLeaders
(
LeaderID int,
Leader varchar(50)
);
INSERT INTO #tblLeaders
(LeaderID, Leader)
SELECT 1, 'Pete' UNION ALL
SELECT 2, 'Jim' UNION ALL
SELECT 3, 'Arthur'
CREATE TABLE #tblMembers
(
MemberID int,
LeaderID int,
Member varchar(50)
);
INSERT INTO #tblMembers
(MemberID, LeaderID, Member)
SELECT 1, 1, 'Frank' UNION ALL
SELECT 2, 1, 'Martha' UNION ALL
SELECT 3, 2, 'Betty' UNION ALL
SELECT 4, 2, 'Mary' UNION ALL
SELECT 5, 3, 'Jerry' UNION ALL
SELECT 6, 3, 'Marcia'
SELECT * FROM #tblLeaders
SELECT * FROM #tblMembers
SELECT *
FROM #tblLeaders
INNER JOIN #tblMembers on #tblLeaders.LeaderID = #tblMembers.LeaderID
ORDER BY Leader, Member
DROP TABLE #tblLeaders
DROP TABLE #tblMembers
The code above gives me:
Leader | Member
Arthur - Jerry
Arthur - Marcia
Jim - Betty
Jim - Mary
Pete - Frank
Pete - Martha
Which is the Leaders in alphabetical order with their Members in alphabetical order
But, what I need is data that comes back like this:
Arthur
Jerry
Marcia
Jim
Betty
Mary
Pete
Frank
Martha
... which is - a row for each team leader, then their team members but still with the Team Leaders appearing alphabetically and with their Team Members appearing below them but sorted alphabetically.
I can do this by putting the team leaders in a cursor, looping through it, putting them in a temp table, finding their team members and putting them in the temp table etc.
Is there a cursor free way of doing this? Thanks for any help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Nick_UK SSCommitted Points: 1909 More actions · Answer 1

you could try this.

SELECT ISNULL(a.Member,a.leader) as [Name]

FROM

(

SELECT leader,NULL as [Member]

FROM #tblLeaders

UNION ALL

SELECT

#tblLeaders.leader,#tblMembers.Member

FROM #tblLeaders

INNER JOIN #tblMembers on #tblLeaders.LeaderID = #tblMembers.LeaderID

) as a

ORDER BY [leader],[Member]

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

I think this is what you're looking for.

SELECT l.LeaderID, l.leader, m.memberid, m.member, ROW_NUMBER() OVER (PARTITION BY l.leader ORDER BY l.leader)

FROM #tblLeaders l

INNER JOIN #tblMembers m on l.LeaderID = m.LeaderID

ORDER BY l.Leader, m.Member;

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 3

Thanks for that, it does the job.

Edit - sorry, this comment was directed to Nick_UK - but it didn't appear where I expected.

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 4

Ed Wagner (5/31/2013)
I think this is what you're looking for.
SELECT l.LeaderID, l.leader, m.memberid, m.member, ROW_NUMBER() OVER (PARTITION BY l.leader ORDER BY l.leader)
FROM #tblLeaders l
INNER JOIN #tblMembers m on l.LeaderID = m.LeaderID
ORDER BY l.Leader, m.Member;

Thanks for your reply - but it doesn't appear to give me a separate row for each leader.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

I guess the next question would be, why did you put leaders and followers in separate tables? It's a lot easier to manage such things if they're all in one table especially if you end up adding another level some day.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 6

Jeff Moden (5/31/2013)
I guess the next question would be, why did you put leaders and followers in separate tables? It's a lot easier to manage such things if they're all in one table especially if you end up adding another level some day.

I did think about that ... but followers can follow multiple leaders and leaders can be followers too. All of which is, I know, manageable in one table but most of the time I simply need a list of leaders/followers so a simple join between the tables gives me that. The only time I need them all, leaders and followers, one after the other is for bulk emailing and I need to store a flag that says 'never email this leader (he is too important) - you must email his followers' and doing all that in one table is beyond my enfeebling brain.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

sku370870 (5/31/2013)
Jeff Moden (5/31/2013)
I guess the next question would be, why did you put leaders and followers in separate tables? It's a lot easier to manage such things if they're all in one table especially if you end up adding another level some day.
I did think about that ... but followers can follow multiple leaders and leaders can be followers too. All of which is, I know, manageable in one table but most of the time I simply need a list of leaders/followers so a simple join between the tables gives me that. The only time I need them all, leaders and followers, one after the other is for bulk emailing and I need to store a flag that says 'never email this leader (he is too important) - you must email his followers' and doing all that in one table is beyond my enfeebling brain.

Ah. Understood. Thank you for the feedback.

The way I've solved such problems in the past is to have positions report to positions (and all the positions are unique) and then assign people to the positions. That makes assigment management very simple and, since that produces a nice Adjacency list, I can easily and quickly create Nested Sets from it (http://www.sqlservercentral.com/articles/Hierarchy/94040/) for some very high speed processing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)