November 25, 2013 at 11:13 pm
Hi,
I have a table like this
coln
1~2
1
1~4~3
2~3
i need to split the values like the given below
coln coln1 col2 coln3
1~2 1 2 null
1 1 null null
1~4~3 1 4 3
2~3 2 3 null
plz help me
November 26, 2013 at 7:43 am
You have been around here long enough to know we need more details to work with here.
To do this I would suspect you need to first split those values using DelimitedSplit8K function. You can find that by reading the article in my signature about splitting strings.
Next you will need to do a cross tab. If the possible column numbers are static you can find the solution in the first article in my signature about cross tabs. If the number is dynamic you will need to read the second article about cross tabs.
If you find that you need more help you should the article in my signature about best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 26, 2013 at 8:56 am
Here is the solution check if this fits your criteria.
create table #t
(
name varchar(max)
)
insert into #t
select '1~2' union
select '1' union
select '1~4~3' union
select '2~3'
select * from #t
SELECT
case when CHARINDEX('~',name,0) > 0 then SUBSTRING(name,0,CHARINDEX('~',name,0)) else null end Column1,
case when CHARINDEX('~',name,CHARINDEX('~',name,0)+1) > CHARINDEX('~',name,0) THEN SUBSTRING(name,CHARINDEX('~',name,0)+1,(CHARINDEX('~',name,CHARINDEX('~',name,0)+1) - (CHARINDEX('~',name,0)+1) ))
when CHARINDEX('~',name,0) > 0 and CHARINDEX('~',name,CHARINDEX('~',name,0)+1) =0 then SUBSTRING(name,CHARINDEX('~',name,0)+1,len(name)) else null end Column2,
case when CHARINDEX('~',name,CHARINDEX('~',name,0)+1) > CHARINDEX('~',name,0) THEN replace(substring(name,CHARINDEX('~',name,CHARINDEX('~',name,0)+1),len(name)),'~','') end Column3
FROM #t
For scripts like please click SQL Server blogs and articles
November 26, 2013 at 9:05 am
Rahul Bhosale (11/26/2013)
Here is the solution check if this fits your criteria.create table #t
(
name varchar(max)
)
insert into #t
select '1~2' union
select '1' union
select '1~4~3' union
select '2~3'
select * from #t
SELECT
case when CHARINDEX('~',name,0) > 0 then SUBSTRING(name,0,CHARINDEX('~',name,0)) else null end Column1,
case when CHARINDEX('~',name,CHARINDEX('~',name,0)+1) > CHARINDEX('~',name,0) THEN SUBSTRING(name,CHARINDEX('~',name,0)+1,(CHARINDEX('~',name,CHARINDEX('~',name,0)+1) - (CHARINDEX('~',name,0)+1) ))
when CHARINDEX('~',name,0) > 0 and CHARINDEX('~',name,CHARINDEX('~',name,0)+1) =0 then SUBSTRING(name,CHARINDEX('~',name,0)+1,len(name)) else null end Column2,
case when CHARINDEX('~',name,CHARINDEX('~',name,0)+1) > CHARINDEX('~',name,0) THEN replace(substring(name,CHARINDEX('~',name,CHARINDEX('~',name,0)+1),len(name)),'~','') end Column3
FROM #t
For scripts like please click SQL Server blogs and articles
You too should look at the links in my signature about splitting strings AND how to do cross tabs. This would not scale well at all. I have to run to a meeting but I will come back and post a solution based on your sample that should perform a LOT better.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply