help with a problem - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

help with a problem

Author

Topic

rezanov
Starting Member

3 Posts

Posted - 2011-06-16 : 10:33:48

Hi.
I have a little problem that is taking too much of my time already.

I am saving on a table a set of, lets call it students, each student is an ID of a student, and the set is just a string separated by ",".

So, this table is something like:

ID Identification ... Students
1 Group1 12,13,15
2 Group2 100,35,56,78

i have already a way of spliting function that creates a temporary table that holds each student ID separated.
My question is, how can i make a representation like:

Group 1 - 12
Group 1 - 13
Group 1 - 15
Group 2 - 100
.....

Can anyone give a little help ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-16 : 10:39:58

It would be easiest/most efficient to add a the Identification column to the query where you are doing the splitting. If you post that query, people on this forum may be able to suggest how to do that. But, if you already have it split as you want it, and just want to use that, your query could be something like this:


SELECT
	y.Identification,
	t.SeparatedID
FROM
	YourOriginalTable y
	INNER JOIN #YourTempTable t ON
		','+y.Students+',' LIKE '%,'+CAST(t.SeparatedId AS VARCHAR(32))+',%'

rezanov
Starting Member

3 Posts

Posted - 2011-06-16 : 10:48:49

It worked ! Thank you.
I didn't quite got understood what you meant by "
It would be easiest/most efficient to add a the Identification column to the query where you are doing the splitting".

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-16 : 12:30:09

All I meant was that perhaps your splitting function can be modified to do the splitting AND generate the query results with an additional Indentification column, thus doing both splitting and adding the Identification column in one shot.

rezanov
Starting Member

3 Posts

Posted - 2011-06-16 : 12:38:13

The split function is this one described on this post: http://geekswithblogs.net/AngelEyes/archive/2007/04/12/111504.aspx#467427

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-16 : 14:20:15

Then you would do it something like this:


select
   Identification,
   Student
from
   YourTable A
   CROSS APPLY
   ( select * from dbo.fnSplit(A.Students,',') ) B(Student)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources