- Details
- Written by: Stanko Milosev
- Category: MS SQL
- Hits: 5295
USE AdventureWorks GO SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE '%EmployeeID%' ORDER BY schema_name, table_name;From here
- Details
- Written by: Stanko Milosev
- Category: MS SQL
- Hits: 5620
EXEC sp_MSforeachtable @command1 = 'select * from ?'
- Details
- Written by: Stanko Milosev
- Category: MS SQL
- Hits: 5697
If you are receiving error:
Â
Invoke or BeginInvoke cannot be called on a control until the window handle has been created..
Â
On new installation of MS SQL 2008 on Windows 7 64 bit, then try to download:
Â
Cumulative Update Package 8 for SQL Server 2008
You don't need to have MS SQL Server installed.
Â
Taken from here.
- Details
- Written by: Stanko Milosev
- Category: MS SQL
- Hits: 5758
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.