August 20, 2008 at 7:05 am
i have a table say for example
c1 c2 c4
--------------
a b 01,05
z y 10,15,20
where c1,c2,c3 are the columns where 4th column is ',' seprated
i want to write a query in such a way, that the ',' separted values slipts and forms a new row with same c1 and c2 data.
c1 c2 c4
a b 01
a b 05
z y 10
z y 15
z y 20
Any one can help me out with this
August 20, 2008 at 7:26 am
mohd.imtiaz (8/20/2008)
i have a table say for examplec1 c2 c4
--------------
a b 01,05
z y 10,15,20
where c1,c2,c3 are the columns where 4th column is ',' seprated
i want to write a query in such a way, that the ',' separted values slipts and forms a new row with same c1 and c2 data.
c1 c2 c4
a b 01
a b 05
z y 10
z y 15
z y 20
Any one can help me out with this
You need a Split function like:
CREATE FUNCTION Split (@list nvarchar(MAX))
RETURNS @returnTable TABLE (stringPart varchar(MAX) NOT NULL) AS
BEGIN
DECLARE @position int
DECLARE @nextPosition int
DECLARE @partLength int
SELECT @position = 0, @nextPosition = 1
WHILE @nextPosition > 0
BEGIN
SELECT @nextPosition = CHARINDEX(',', @list, @position + 1)
SELECT @partLength = CASE WHEN @nextPosition > 0
THEN @nextPosition
ELSE LEN(@list) + 1
END - @position - 1
INSERT INTO @returnTable (stringPart)
VALUES (CONVERT(varchar, SUBSTRING(@list, @position + 1, @partLength)))
SELECT @position = @nextPosition
END
RETURN
END
GO
Then, (I assume you are on SQL Server 2005), you can use crossapply like:
SELECT c1, c2, stringPart FROM myTable CROSS APPLY Split(c4)
(where myTable is your original table)
Regards,
Andras
August 21, 2008 at 1:03 am
Hi Andras,
Thanks for your spontaneous reply, it was really kind of you.
I tried your query in sql 2005 it was woring fine over there.
Is it possible in Sql 2K, tried using Inner Join instead of Cross Apply but was not successful in that.
Can you help me out with this too.
Thanks and Regards
Mohammed Imtiaz
August 21, 2008 at 1:58 am
mohd.imtiaz (8/21/2008)
Hi Andras,Thanks for your spontaneous reply, it was really kind of you.
I tried your query in sql 2005 it was woring fine over there.
Is it possible in Sql 2K, tried using Inner Join instead of Cross Apply but was not successful in that.
Can you help me out with this too.
Thanks and Regards
Mohammed Imtiaz
Well, it was a 2005 forum. CROSS APPLY was introduced in SQL Server 2005, so for 2000 the solution is more tricky.
to create a test table (for others to play with :))
create table myTable (c1 char, c2 char, c4 varchar(100))
insert into myTable values ('a','b','01,05')
insert into myTable values ('z','y','10,15,20')
Good news is that there is no need for the function, but let's make use of a tally table:
select top 1000 identity(int, 1,1) as nr into #tally from syscolumns a, syscolumns b
Once we have the tally table, we can do the following (basically we will be ripping out the relevant substrings from the c4 column starting from all the positions in the c4 column, but only where there is a comma (and we pad the c4 column with commas at the beginning and end to make life easier) (the solution is inspired by http://weblogs.sqlteam.com/peterl/archive/2007/10/15/Cross-apply-in-SQL-Server-2000.aspx)
So the solution is:
SELECT c1, c2,
SUBSTRING(','+c4+',', t.nr+1, CHARINDEX(',', ',' + c4 + ',', t.nr + 1) - t.nr - 1)
FROM myTable JOIN #tally AS t ON SUBSTRING(',' + c4 + ',', t.nr, 1) = ','
WHERE t.nr < LEN(',' + c4 + ',')
This gives, as expected:
c1 c2
---- ---- ----
a b 01
a b 05
z y 10
z y 15
z y 20
ps: help for the future: if you use SQL Server 2000 post to the SQL Server 2000 forum 🙂 SQL Server 2005 introduced many features that, while make life simpler, do not work on 2000.
Regards,
Andras
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply