April 20, 2010 at 6:10 am
All,
I have a requirement.
create table #temp
(name varchar(50)
)
insert into #temp values ('karthik~gokul~siva~magesh~')
insert into #temp values ('kar~go~sa~mag~')
insert into #temp values ('k~g~s~m~')
Expected output:
c1 c2 c3 c4
karthik gokul siva magesh
kar go sa mag
k g s m
I have already posted the same question under sql2005. But i have to use PIVOT to do this. But i have to do this both in SQL 2000 & SQL 2005.
Inputs are welcome!
karthik
April 20, 2010 at 6:42 am
You could use the split function I posted in the 2005 forum and do it this way:
DECLARE @temp TABLE (
name VARCHAR(50)
)
INSERT INTO @temp VALUES ('karthik~gokul~siva~magesh~')
INSERT INTO @temp VALUES ('kar~go~sa~mag~')
INSERT INTO @temp VALUES ('k~g~s~m~')
SELECT *,
[1] = (
SELECT Value
FROM dbo.FSplit((SELECT name FROM @temp AS A WHERE A.name = T.name), '~')
WHERE id = 1
),
[2] = (
SELECT Value
FROM dbo.FSplit((SELECT name FROM @temp AS A WHERE A.name = T.name), '~')
WHERE id = 2
),
[3] = (
SELECT Value
FROM dbo.FSplit((SELECT name FROM @temp AS A WHERE A.name = T.name), '~')
WHERE id = 3
),
[4] = (
SELECT Value
FROM dbo.FSplit((SELECT name FROM @temp AS A WHERE A.name = T.name), '~')
WHERE id = 4
)
FROM @temp AS T
-- Gianluca Sartori
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply