Split Data

[lyric0n]

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

 

[onpnt]

Many examples out there

http://www.google.com/search?hl=en&q=split+delimiter+transact-sql

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[ESquared]

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]

 

[lyric0n]

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

 

[ESquared]

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]

 

[Lamprey13]

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:

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:

123
456
789
987
654
321

-Ryan