Showing posts with label apart. Show all posts
Showing posts with label apart. Show all posts

Tuesday, February 14, 2012

Breaking apart Column into rows

I have a column that has text delimited by a percent sign that I wish
to turn into rows.
Example:
A column contains ROBERT%CAMARDA, I want to turn that into two rows,
one row with ROBERT and antoher row with CAMARDA.
I will have source rows that have zero, one, or many percent sign
delimiters that will correspond to that many rows (One percent sign
will create 2 rows, 2 percent signs will create 3 rows and so forth).

Any thoughts?
TIA
Robrcamarda (rcamarda@.cablespeed.com) writes:
> I have a column that has text delimited by a percent sign that I wish
> to turn into rows.
> Example:
> A column contains ROBERT%CAMARDA, I want to turn that into two rows,
> one row with ROBERT and antoher row with CAMARDA.
> I will have source rows that have zero, one, or many percent sign
> delimiters that will correspond to that many rows (One percent sign
> will create 2 rows, 2 percent signs will create 3 rows and so forth).

Have a look at an article on my web site:
http://www.sommarskog.se/arrays-in-sql.html.

Specifically, look at "List-of-strings" and "Unpacking a table column".

You did not say which version of SQL Server you are using. The article
is written for SQL 2000. SQL 2005 provides a new operator CROSS APPLY
which is good for the column bit. In SQL 2000, you will have to run
it row by row.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Monday, February 13, 2012

Break Apart Data Using While Loop

I need to break apart the following data into multiple records but I am not
sure how to write the code. The record identifier is the ;
RecordID DataInfo
1 "abc", "def"; "ghi", "jkl"
2 "abc", "def", "ghi"; "jkl", "mno"
Data in a new table will be
RecordID DataInfo
1 "abc", "def"
1 "ghi", "jkl"
2 "abc", "def", "ghi"
2 "jkl", "mno"
Thanks for the help!If the delimiter ; only appears once in the DataInfo column you could
do something like this...
Create Table #TableA
(
Record Integer,
DataInfo varchar(50)
)
Insert Into #TableA
Select 1, '"abc", "def"; "ghi", "jkl"'
Union
Select 2, '"abc", "def", "ghi"; "jkl", "mno" '
Create Table #TableB
(
Record Integer,
DateInfo Varchar(50)
)
Insert Into #TableB
Select Record, Replace((Ltrim(Rtrim(Left(DataInfo,
(CharIndex(';',DataInfo)))))), ';','')
>From #TableA
Union
Select Record, Replace((Ltrim(Rtrim(Right(DataInfo,
(CharIndex(';',Reverse(DataInfo))))))), ';','')
>From #TableA
Select * From #TableA
Select * From #TableB
Drop Table #TableA
Drop Table #TableB
HTH
Barry|||Sorry my example wasn't specific enough. The delimiter can appear multiple
times in the column.
"Barry" wrote:

> If the delimiter ; only appears once in the DataInfo column you could
> do something like this...
>
> Create Table #TableA
> (
> Record Integer,
> DataInfo varchar(50)
> )
> Insert Into #TableA
> Select 1, '"abc", "def"; "ghi", "jkl"'
> Union
> Select 2, '"abc", "def", "ghi"; "jkl", "mno" '
> Create Table #TableB
> (
> Record Integer,
> DateInfo Varchar(50)
> )
>
> Insert Into #TableB
> Select Record, Replace((Ltrim(Rtrim(Left(DataInfo,
> (CharIndex(';',DataInfo)))))), ';','')
> Union
> Select Record, Replace((Ltrim(Rtrim(Right(DataInfo,
> (CharIndex(';',Reverse(DataInfo))))))), ';','')
>
> Select * From #TableA
> Select * From #TableB
> Drop Table #TableA
> Drop Table #TableB
>
> HTH
> Barry
>|||
As an alternative you can use a numbers table
as in http://www.aspfaq.com/show.asp?id=2516
and can do this
select Record,
ltrim(substring(DataInfo,
Number,
charindex(';',
DataInfo + ';',
Number) - Number)) as DataInfo
from #TableA
inner join Numbers on Number between 1 and len(DataInfo) + 1
and substring(';' + DataInfo, Number, 1) = ';'|||Or regular expressions...
http://www.sqlservercentral.com/col...oolkitpart2.asp
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:37F08AA1-FC30-42C9-B50F-50B0FA9FD7B5@.microsoft.com...
>I need to break apart the following data into multiple records but I am not
> sure how to write the code. The record identifier is the ;
> RecordID DataInfo
> 1 "abc", "def"; "ghi", "jkl"
> 2 "abc", "def", "ghi"; "jkl", "mno"
> Data in a new table will be
> RecordID DataInfo
> 1 "abc", "def"
> 1 "ghi", "jkl"
> 2 "abc", "def", "ghi"
> 2 "jkl", "mno"
> Thanks for the help!
>