Splitting data in a cell during a query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Splitting data in a cell during a query

Author

Topic

JFarr
Starting Member

1 Post

Posted - 2009-06-22 : 19:23:18

Hi All,
I'm rather new to SQL, so please be gentle :).

What I have are two tables.
In the first table, I have columns:
FirstName, LastName, RegionalResource, etc...

In the RegionalResource column, there can be multiple regions, seperated by a comma. For example, New York, Long Island.

Now in the second table, there is a Region column, with the region names a little different. For example, CI - New York. This is a single value field.

Now what I want to do is for each of the values in Table 1, Region, I need to get the counts from Table 2. Is there a way to split the data from the first table in a query to perform the quert to get the counts?

I have this, but it is not quite right. For one, it is not performing a count yet.. And two, it is only returning names with one region associated with them.

Here is the query so far-

Select FD.Troubleshooter, TM.Region, FD.Region  From TeamMember TM
Join dbo.[*FailureData] FD On FD.Troubleshooter = TM.TeamMember And (FD.Region like '%' + TM.Region + '%')
Group By FD.Troubleshooter, TM.Region, FD.Region
order by FD.Troubleshooter asc

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-24 : 02:33:59

Try

Select FD.Troubleshooter, TM.Region, FD.Region From TeamMember TM
Join dbo.[*FailureData] FD On FD.Troubleshooter = TM.TeamMember And (','+FD.Region+',' like '%,' + TM.Region + ',%')
Group By FD.Troubleshooter, TM.Region, FD.Region
order by FD.Troubleshooter asc

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources