locked
Simple yet puzzling query RRS feed

  • Question

  • Hello.  I am struggling with a query that I know can be done but I'm just not seeing it.

    MeetState=state where the track meet was held
    TeamName=name of the team (of course)
    TeamState=home state of the team

    MeetState     TeamName      TeamState
    FL                 Houston           TX
    FL                 Tampa             FL
    FL                 Detroit             MI
    TX                Dallas              TX
    TX                Houston           TX
    TX                Austin              TX
    NY                Dallas              TX
    NY                Tampa             FL
    NY                Phoenix           AZ
    NY                Albany             NY

    What I am needing is a list of the track meets that were attended by an out-of-state team, along with a comma-delimited list of those teams.

    With the above data, the results would look like this:

    FL        Houston,Detroit
    NY       Dallas,Tampa,Phoenix
    (the TX meet would not be listed because all of the teams were also from TX)

    I know that for someone out there this is a simple task and I am looking forward to learning something new.

    Thank you.

    Tuesday, February 2, 2010 8:21 AM

Answers

  • DECLARE	@Sample TABLE
    	(
    		MeetState CHAR(2) NOT NULL,
    		TeamName VARCHAR(20) NOT NULL,
    		TeamState CHAR(2) NOT NULL
    	)
    
    INSERT	@Sample
    	(
    		MeetState,
    		TeamName,
    		TeamState
    	)
    VALUES	('FL', 'Houston', 'TX'),
    	('FL', 'Tampa',   'FL'),
    	('FL', 'Detroit', 'MI'),
    	('TX', 'Dallas',  'TX'),
    	('TX', 'Houston', 'TX'),
    	('TX', 'Austin',  'TX'),
    	('NY', 'Dallas',  'TX'),
    	('NY', 'Tampa',   'FL'),
    	('NY', 'Phoenix', 'AZ'),
    	('NY', 'Albany',  'NY')
    
    SELECT		ms.MeetState,
    		STUFF(team.Name, 1, 1, '') AS TeamNames
    FROM		(
    			SELECT		MeetState
    			FROM		@Sample
    			WHERE		MeetState <> TeamState
    			GROUP BY	MeetState
    		) AS ms
    CROSS APPLY	(
    			SELECT DISTINCT	TOP(2147483647)
    					',' + s.TeamName
    			FROM		@Sample AS s
    			WHERE		s.MeetState = ms.MeetState
    					AND s.TeamState <> ms.MeetState
    			ORDER BY	',' + s.TeamName
    			FOR XML		PATH('')
    		) AS team(Name)
    ORDER BY	ms.MeetState
    • Marked as answer by rdprecure Tuesday, February 2, 2010 9:20 AM
    Tuesday, February 2, 2010 8:37 AM

All replies

  • ;

     

    with t as

     

     

    (

     

    select distinct Meetstate from tablename

     

    )

     

    select MeetState,stuff((select ',' + TeamName from TableName where MeetState = t.MeetState and TeamState <> MeetState for XML path('')),1,1,'') as Teams

     

    from t

    • Edited by ramireddy Tuesday, February 2, 2010 8:42 AM
    Tuesday, February 2, 2010 8:32 AM
  • I just now observed that... when i am writing this post, I am in meeting with my team lead and he is asking me to concentrate on meeting... :)

    Tuesday, February 2, 2010 8:44 AM
  • Thanks Peso.  That worked beautifully.  And I learned something very useful.
    Tuesday, February 2, 2010 9:21 AM
  • There's no reason for the DISTINCT TOP (2147483647) stuff, is there Peso?

    It's possible that if there are duplicate TeamNames for a MeetState, then a DISTINCT would be warranted, but I don't think T-SQL needs the TOP(2147483647) to do the ORDER BY...

    CROSS APPLY
      (SELECT DISTINCT ','+s.TeamName
       FROM @Sample S
       WHERE s.MeetState=ms.MeetState
           AND s.TeamState<>ms.MeetState
       ORDER BY s.TeamName
       FOR XML PATH('')) Team(Name)


    --Brad (My Blog)
    Tuesday, February 2, 2010 2:33 PM