SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 need help in Split
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

mkhalid
Starting Member

9 Posts

Posted - 10/14/2007 :  03:33:17  Show Profile  Reply with Quote
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)

Singapore
17437 Posts

Posted - 10/14/2007 :  03:35:28  Show Profile  Reply with Quote
see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648


KH
Time is always against us

Go to Top of Page

mkhalid
Starting Member

9 Posts

Posted - 10/14/2007 :  03:37:45  Show Profile  Reply with Quote
tried it, didnt seem to get it working.
Go to Top of Page

mkhalid
Starting Member

9 Posts

Posted - 10/14/2007 :  03:40:24  Show Profile  Reply with Quote
there are two columns the first one is an identity productid and the second column has comma delimitted data.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17437 Posts

Posted - 10/14/2007 :  03:44:35  Show Profile  Reply with Quote
the number of value in the comma delimited data is it always 3 ?


KH
Time is always against us

Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/14/2007 :  05:25:16  Show Profile  Reply with Quote
Not easy in SQL 2000 if there are a variable number of items.

Any chance that you are using SQL 2005?

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/14/2007 :  10:07:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 10/14/2007 :  10:18:38  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/14/2007 :  10:51:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/14/2007 :  10:54:14  Show Profile  Reply with Quote
"I have a way to do a "cross apply" in SQL Server 2000 without function."

Heretofore thought to be impossible ... <BaitedBreath!>
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17437 Posts

Posted - 10/14/2007 :  12:35:12  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/14/2007 :  12:41:44  Show Profile  Reply with Quote
Sorry, Peso won't be in tomorrow, I've given him a job in marketing
Go to Top of Page

mkhalid
Starting Member

9 Posts

Posted - 10/14/2007 :  13:20:39  Show Profile  Reply with Quote
comma delimitted data is a varchar(500) therefore it has more than 3 values.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/14/2007 :  14:13:52  Show Profile  Reply with Quote
Are you using SQL 2005?

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 10/14/2007 :  14:26:06  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/14/2007 :  15:23:21  Show Profile  Reply with Quote
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
Go to Top of Page

mkhalid
Starting Member

9 Posts

Posted - 10/14/2007 :  16:01:37  Show Profile  Reply with Quote
found the answer here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89817&SearchTerms=split
works great.
Go to Top of Page

mkhalid
Starting Member

9 Posts

Posted - 10/14/2007 :  16:06:05  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 10/15/2007 :  02:22:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 + ',')


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"

Edited by - SwePeso on 10/15/2007 02:58:24
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 10/15/2007 :  02:27:10  Show Profile  Visit spirit1's Homepage  Reply with Quote
interesting!

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

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 10/15/2007 :  02:29:07  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000