| Author |
 Topic  |
|
mkhalid
Starting Member
9 Posts |
 Posted - 10/14/2007 : 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)
Singapore
16769 Posts |
|
|
mkhalid
Starting Member
9 Posts |
Posted - 10/14/2007 : 03:37:45
|
tried it, didnt seem to get it working.
|
 |
|
|
mkhalid
Starting Member
9 Posts |
Posted - 10/14/2007 : 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)
Singapore
16769 Posts |
Posted - 10/14/2007 : 03:44:35
|
the number of value in the comma delimited data is it always 3 ?
KH
Time is always against us
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/14/2007 : 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
Sweden
29156 Posts |
Posted - 10/14/2007 : 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
Slovenia
11741 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 10/14/2007 : 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
United Kingdom
22191 Posts |
Posted - 10/14/2007 : 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)
Singapore
16769 Posts |
Posted - 10/14/2007 : 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
Time is always against us
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/14/2007 : 12:41:44
|
Sorry, Peso won't be in tomorrow, I've given him a job in marketing  |
|
|
|
mkhalid
Starting Member
9 Posts |
Posted - 10/14/2007 : 13:20:39
|
| comma delimitted data is a varchar(500) therefore it has more than 3 values. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/14/2007 : 14:13:52
|
Are you using SQL 2005?
Kristen |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/14/2007 : 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 |
|
|
mkhalid
Starting Member
9 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 10/15/2007 : 02:22:20
|
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 |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
Topic |
|
need help in Split
