August 17, 2011 at 6:00 am
Hi All,
I have to split the below signle row into multiple columns.Since i have to use the same query in sql 2000, i don't want to use PIVOT or some SQL 2005 specific functions.
declare @_sP varchar(1024)
select @_sP = 'D,20.5%,10.25%,A account is being linked to B account'
I used Tally table to split the row and it worked as expected.
i.e
D
20.5%
10.25%
A account is being linked to B account
But this is not my expected output. I want something like
D 20.5% 10.25% A account is being linked to B account
in a seperate columns.
Inputs are welcome!
karthik
August 17, 2011 at 6:05 am
I don't want to appear to be rude, but with 2,087 visits on this forum you definitely aware of how to post questions here. If not, link in my signature will help to revive this knowledge.
Please provide ddl, sample data, expected results and whatever query you've tried so far to achieve it.
August 17, 2011 at 6:12 am
I never mistake your words..you said the right things..actually..i also suggest the same to other posters..
i just receive only one value through input parameter which i posted in my question.
declare @_sP varchar(1024)
select @_sP = 'D,20.5%,10.25%,A account is being linked to B account'
I have to splt it and show it under 4 different columns.
say for example
colA colB colC colD
D 20.5% 10.25% A account is being linked to B account
Thats all.
karthik
August 17, 2011 at 6:19 am
DECLARE @Parameter VARCHAR(255)
SELECT @Parameter = ',' + 'D,20.5%,10.25%,A account is being linked to B account'+ ','
SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)
FROM dbo.Tally WHERE N < LEN(@Parameter) AND SUBSTRING(@Parameter,N,1) = ',' --Notice how we find the comma
karthik
August 17, 2011 at 6:39 am
If you are splitting just one single value, anything would do!
You can use just SET operators to check next position of comma and to get one value after another.
If you really like to use Tally table to split string (keep in mid that in the given case will not be possible to see performance difference), you can use the following:
;WITH split
AS
(
SELECT ROW_NUMBER() over (order by (select null)) RN,SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1) val
FROM dbo.Tally WHERE N < LEN(@Parameter) AND SUBSTRING(@Parameter,N,1) = ',' --Notice how we find the comma
)
select max(case when rn = 1 then val else null end) as colA
,max(case when rn = 2 then val else null end) as colB
,max(case when rn = 3 then val else null end) as colC
,max(case when rn = 4 then val else null end) as colD
from split
August 17, 2011 at 6:41 am
SET ???
karthik
August 17, 2011 at 6:43 am
...the position of comma may vary..
say for example...
D,20.5454545454%,178780.2323225%,A account is being linked to B account'
karthik
August 17, 2011 at 6:46 am
Yep, as simple as this:
declare @_sP varchar(1024)
select @_sP = 'D,20.5%,10.25%,A account is being linked to B account'
declare @cA varchar(1024),@cB varchar(1024),@cC varchar(1024),@cD varchar(1024),@p int
SET @cA=@_sP
SET @p=CHARINDEX(',',@cA); IF @p>1 SELECT @cB=SUBSTRING(@cA,@p+1,1024), @cA = LEFT(@cA, @p-1)
SET @p=CHARINDEX(',',@cB); IF @p>1 SELECT @cC=SUBSTRING(@cB,@p+1,1024), @cB = LEFT(@cB, @p-1)
SET @p=CHARINDEX(',',@cC); IF @p>1 SELECT @cD=SUBSTRING(@cC,@p+1,1024), @cC = LEFT(@cC, @p-1)
select @cA colA, @cB colB, @cC colC, @cD colD
August 17, 2011 at 8:21 am
I DID SOMETHING LIKE BELOW....
declare @_sPlatform varchar(1024)
select @_sPlatform = 'D,20987987.5%,10.25876767868%,A account is being linked to B account'
declare @t1 varchar(255), @start_pos int
declare @t2 varchar(255)
declare @t3 varchar(255)
declare @t4 varchar(255)
select @t1 = substring(@_sPlatform,1, charindex(',',@_sPlatform)-1)
select @t1
--
select @start_pos = datalength(@t1) +2
select @_sPlatform = substring(@_sPlatform , @start_pos, datalength(@_sPlatform))
select @t2 = substring(@_sPlatform,1, charindex(',',@_sPlatform)-1)
select @t2
--
select @start_pos = datalength(@t2) +2
select @_sPlatform = substring(@_sPlatform , @start_pos, datalength(@_sPlatform))
select @t3 = substring(@_sPlatform,1, charindex(',',@_sPlatform)-1)
select @t3
--
select @start_pos = datalength(@t3) +2
select @_sPlatform = substring(@_sPlatform , @start_pos, datalength(@_sPlatform))
select @t4 = substring(@_sPlatform,1, datalength(@_sPlatform))
select @t4
karthik
August 17, 2011 at 8:33 am
I just came to know i need to implement the same against a table data.
CREATE TABLE W
(
ID INT,
val varchar(255)
)
insert into W
select 100, 'D,20.5%,10.25%,A account is being linked to B account'
union
select 200, 'R,15.5%,12.2%,X account is being linked to Y account'
union
select 300, 'T,5.2%,2.2%,Z account is being linked to Z1 account'
union
select 400, 'A,0.232%,1.232%,ABC account is being linked to XYZ account'
union
select 500, 'X,7.2%,6.2%,Karthik account is being linked to Karthik1 account'
karthik
August 17, 2011 at 8:33 am
Not bad, but try to set your input variable to:
select @_sPlatform = 'D,20987987.5%,A account is being linked to B account'
Sample I've gave you would work;-)
However, using Tally method would work for all possible combinations eg ',,,,'.
August 17, 2011 at 8:35 am
karthikeyan-444867 (8/17/2011)
I just came to know i need to implement the same against a table data.
CREATE TABLE W
(
ID INT,
val varchar(255)
)
insert into W
select 100, 'D,20.5%,10.25%,A account is being linked to B account'
union
select 200, 'R,15.5%,12.2%,X account is being linked to Y account'
union
select 300, 'T,5.2%,2.2%,Z account is being linked to Z1 account'
union
select 400, 'A,0.232%,1.232%,ABC account is being linked to XYZ account'
union
select 500, 'X,7.2%,6.2%,Karthik account is being linked to Karthik1 account'
Came as expected :hehe:
Use Tally method with "Group By ID"
August 17, 2011 at 8:37 am
Yeah..Thats correct..and good catch...
But the requirement is to do this against the table data instead of input parameter
..
karthik
August 17, 2011 at 8:38 am
Tally with GROUP BY ...?
i am not getting you...
karthik
August 17, 2011 at 8:38 am
You are posting to fast 😀
Check the previous post!
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply