Details

Written by: Stanko Milosev

Category: MS SQL

Published: 03 February 2010

Last Updated: 22 November 2012

Hits: 5759

On the MS SQL forum I saw interesting question, which goes like this:

 

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.

 

And, aswer is:

 

 

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

This just note to my self, I didn't test, but I maybe need it in the future.

---

Actually I found one better way:

SELECT
   t1.TeamID,
   MemberList = substring((SELECT ( ', ' + FirstName )
                           FROM TeamInfo t2
                           WHERE t1.TeamID = t2.TeamID
                           ORDER BY 
                              TeamID,
                              FirstName
                           FOR XML PATH( '' )
                          ), 3, 1000 )FROM TeamInfo t1
GROUP BY TeamID

From here.

Details

Written by: Stanko Milosev

Category: MS SQL

Published: 21 January 2010

Last Updated: 04 July 2013

Hits: 5485

If you have problem with your SQL Server, like it locks table, or your select statement takes too long, or executes never, tham try something like:

 

ALTER DATABASE MyTableName
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyTableName
SET READ_COMMITTED_SNAPSHOT ON

Solution I found here, also you can read this article from Microsoft.

Details

Written by: Stanko Milosev

Category: MS SQL

Published: 02 September 2009

Last Updated: 13 September 2012

Hits: 5445

If you are using Microsoft drivers for connection to MS SQL 2008, and receiving this kind of error, then, you will need to download new drivers from here. After that you will need to download sqlncli.msi (you will get the link in sqlsrv_errors())