February 13, 2013 at 3:10 pm
I have a table that has
ID Name
123 12345-abcd, 6789-efgh
456 R11223344-abc, 223366-rreett, A45566-ppooit
I need to take the "name" column and split it to muliple columns whenever I find a comma (,). I have found functions that do this, but they return a table variable. I need something that I can use that when used I can retain the relationship between the split columns and the ID columns. Can anyone lend a hand?
Thanks!
February 13, 2013 at 3:36 pm
Tara D (2/13/2013)
I have a table that hasID Name
123 12345-abcd, 6789-efgh
456 R11223344-abc, 223366-rreett, A45566-ppooit
I need to take the "name" column and split it to muliple columns whenever I find a comma (,). I have found functions that do this, but they return a table variable. I need something that I can use that when used I can retain the relationship between the split columns and the ID columns. Can anyone lend a hand?
Thanks!
Take a look at the link in my signature about splitting strings.
_______________________________________________________________
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/
February 13, 2013 at 3:58 pm
That will get you rows. You'll also need a Cross-tab to bring it back as columns:
WITH cte ( ID, Name )
AS ( SELECT 123,
'12345-abcd, 6789-efgh'
UNION ALL
SELECT 456,
'R11223344-abc, 223366-rreett, A45566-ppooit'
)
SELECT ID,
MAX(CASE WHEN d.ItemNumber = 1 THEN Item
ELSE ''
END) AS Item1,
MAX(CASE WHEN d.ItemNumber = 2 THEN Item
ELSE ''
END) AS Item2,
MAX(CASE WHEN d.ItemNumber = 3 THEN Item
ELSE ''
END) AS Item3
FROM cte
CROSS APPLY dbo.DelimitedSplit8K(Name, ',') d
GROUP BY ID;
As you can plainly see with this technique you have to know the max number of columns ahead of time. If you do not then you can start looking into dynamic cross tabs. In addition to Jeff Moden's splitter article Sean referenced you can also find his article on dynamic cross tabs on this site.
The bigger question in my mind is, did you design this table or are you just being asked to work with it? If the former, might I suggest a shift in gears towards normalizing the data into rows instead of storing 1-n values in a column.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 13, 2013 at 6:38 pm
perhaps this might help:
declare @tbl table (id int,string varchar(512))
Insert into @tbl
SELECT 123,
'12345-abcd, 6789-efgh'
UNION ALL
SELECT 456,
'R11223344-abc, 223366-rreett, A45566-ppooit'
select * from @tbl
;with cte(id,position,string,remaining)
as
(
select id,charindex(',',string), left(string,charindex(',',string)-1),ltrim(right(string,len(string)-charindex(',',string)))from @tbl
union all
select id,charindex(',',remaining),case when charindex(',',remaining)=0 then ltrim(right(remaining,len(remaining)-charindex(',',remaining))) else left(remaining,charindex(',',remaining)-1) end,ltrim(right(remaining,len(remaining)-charindex(',',remaining)))from cte where string!=remaining
)
select id,string from cte order by id
February 14, 2013 at 7:30 am
sqlbi.vvamsi (2/13/2013)
perhaps this might help:declare @tbl table (id int,string varchar(512))
Insert into @tbl
SELECT 123,
'12345-abcd, 6789-efgh'
UNION ALL
SELECT 456,
'R11223344-abc, 223366-rreett, A45566-ppooit'
select * from @tbl
;with cte(id,position,string,remaining)
as
(
select id,charindex(',',string), left(string,charindex(',',string)-1),ltrim(right(string,len(string)-charindex(',',string)))from @tbl
union all
select id,charindex(',',remaining),case when charindex(',',remaining)=0 then ltrim(right(remaining,len(remaining)-charindex(',',remaining))) else left(remaining,charindex(',',remaining)-1) end,ltrim(right(remaining,len(remaining)-charindex(',',remaining)))from cte where string!=remaining
)
select id,string from cte order by id
I would suggest that you also take a look at the link in my signature about splitting string. The recursive cte method works but it doesn't scale terribly well.
_______________________________________________________________
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply