Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Split Data

Status
Not open for further replies.

lyric0n

Technical User
Dec 28, 2005
74
Hello,

I need to have data split, but apparently SQL doesn't support this by default. Below is an example of what I have and need...

IDKey | EmpID
1 | 101
2 | 101,102
3 | 103
4 | 101,103
...

What I need returned is just a list of unique employee ID's, so just a list of 101,102, and 103.

I've tried tinkering with sub-select statements, but without much luck..Any help is appreciated, thanks,
Chris
 
See "passing a list of values to a stored procedure" FAQs, parts 1, 2, and 2 in the FAQs in this forum.

Also see the CROSS APPLY command in SQL 2005 if you're using that.

<soapbox>

And last but most important of all [red]stop using multivalued columns[/red] because [blue]IT IS TERRIBLE DATABASE DESIGN[/blue].

</soapbox>

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]
 
ESquared, unfortunately, I didn't design this table, I am just using it...

Okay, so now I have a function which I can call like below...

SELECT * FROM Split('abc,def,123',',')

And it returns abc,def,123 on their own rows. How would I get this to input a column from another SQL table?

Thanks,
Chris
 
Unfortunately you'll have to do this one row at a time with a loop or cursor.

Like I said, multivalued columns are just bad news.

Do you have SQL 2005?

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]
 
If you have 2005 you can use CROSS APPLY to acheive the result you are looking for. For example, I have a function that splits a list of integers:
Code:
DECLARE @T TABLE (List VARCHAR(50))

INSERT @T
SELECT '123,456,789'
UNION ALL SELECT '987,654,321'

SELECT 
	Temp.Val
FROM 
	@T AS T
CROSS APPLY 
	dbo.fn_SplitInteger(List, ',', DEFAULT) AS Temp
Result:
Code:
123
456
789
987
654
321
-Ryan
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top