Data into fields

Details

Category: MS SQL

Published on Wednesday, 03 February 2010 07:02

Hits: 390

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.

Alt + F1

Details

Category: MS SQL

Published on Wednesday, 29 April 2009 08:21

Hits: 408

Why is SQL Server 2008 blocking SELECT?

Details

Category: MS SQL

Published on Thursday, 21 January 2010 15:13

Hits: 427

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.

List all tables in a database.

Details

Category: MS SQL

Published on Wednesday, 03 December 2008 10:02

Hits: 458

How to get list of all tables in MS SQL Server?

 SELECT *
    FROM information_schema.Tables
where table_type = 'BASE TABLE'

From here.