- Details
- Written by: Stanko Milosev
- Category: MS SQL
- Hits: 5123
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
- Hits: 4891
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
- Hits: 4904
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())