need help in Split - 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 2000 Forums

SQL Server Development (2000)

need help in Split

Author

Topic

mkhalid
Starting Member

9 Posts

Posted - 2007-10-14 : 03:33:17

Hi,

I need some help in splitting up my data.

table is as follows
ProductID ComboProductIDs
123 125,124,126
124 127,21,245

I need a query or function to display the data as follows

ProductID ComboProductIDs
123 125
123 124
123 126
124 127
124 21
124 245

like it was never comma delimitted in the first place.
is this possible.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-14 : 03:35:28

see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

KH
[spoiler]Time is always against us[/spoiler]

mkhalid
Starting Member

9 Posts

Posted - 2007-10-14 : 03:37:45

tried it, didnt seem to get it working.

mkhalid
Starting Member

9 Posts

Posted - 2007-10-14 : 03:40:24

there are two columns the first one is an identity productid and the second column has comma delimitted data.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-14 : 03:44:35

the number of value in the comma delimited data is it always 3 ?

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2007-10-14 : 05:25:16

Not easy in SQL 2000 if there are a variable number of items.

Any chance that you are using SQL 2005?

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-14 : 10:07:25

Can you wait until tomorrow? I have a way to do a "cross apply" in SQL Server 2000 without function.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-14 : 10:18:38

that should be interesting.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-14 : 10:51:59

Could be. It is a variant of the code Kristen borrowed from me to do V3 split function.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-14 : 10:54:14

"I have a way to do a "cross apply" in SQL Server 2000 without function."

Heretofore thought to be impossible ... <BaitedBreath!>

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-14 : 12:35:12

quote:
Originally posted by Peso

Can you wait until tomorrow? I have a way to do a "cross apply" in SQL Server 2000 without function.

E 12°55'05.25"
N 56°04'39.16"

I gotta see this.

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2007-10-14 : 12:41:44

Sorry, Peso won't be in tomorrow, I've given him a job in marketing

mkhalid
Starting Member

9 Posts

Posted - 2007-10-14 : 13:20:39

comma delimitted data is a varchar(500) therefore it has more than 3 values.

Kristen
Test

22859 Posts

Posted - 2007-10-14 : 14:13:52

Are you using SQL 2005?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-14 : 14:26:06

well since he posted in 2000 forum i doubt it

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Kristen
Test

22859 Posts

Posted - 2007-10-14 : 15:23:21

Indeed, that's why I asked

But I got no answer the first time, so I have now asked again. If the OP is on SQL 2005 then Cross Apply would make the job easy, but if its SQL2000 then its going to be Loops, Cursors and Peso's "String + Gum" ...

Kristen

mkhalid
Starting Member

9 Posts

Posted - 2007-10-14 : 16:01:37

found the answer here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89817&SearchTerms=split
works great.

mkhalid
Starting Member

9 Posts

Posted - 2007-10-14 : 16:06:05

quote:
Originally posted by mkhalid

found the answer here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89817&SearchTerms=split
works great.

sorry i missed the sql post, im using sql 2000.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-15 : 02:22:20

[code]DECLARE @Sample TABLE (Col1 VARCHAR(6), Col3 VARCHAR(200))

INSERT @Sample
SELECT '123', '125,124,126' UNION ALL
SELECT '124', '127,21,245'

--SELECT Col1,
-- Data
--FROM @Sample
--CROSS APPLY fnParseList(',', Col3)

SELECT a.Col1,
SUBSTRING(',' + a.Col3 + ',', n.Number + 1, CHARINDEX(',', ',' + a.Col3 + ',', n.Number + 1) - n.Number - 1) AS [Value]
FROM @Sample AS a
INNER JOIN master..spt_values AS n ON n.Type = 'p'
WHERE n.Number > 0
AND SUBSTRING(',' + a.Col3 + ',', n.Number, 1) = ','
AND n.Number < LEN(',' + a.Col3 + ',')[/code]

Also blogged at http://weblogs.sqlteam.com/peterl/archive/2007/10/15/Cross-apply-in-SQL-Server-2000.aspx

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-15 : 02:27:10

interesting!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-15 : 02:29:07

you should put this on dotnetkicks, peter.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources