Conversion of CSV to Single Records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Conversion of CSV to Single Records

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-09-25 : 07:24:02

Bob Meason writes "I have used Rob Volks parsing CSV to a single record using the Tally table. It worked flawlessly as detailed.

However, here's a twist: What would you do if you wanted to create a single record with TWO words (2 CSV values). Like this:

Author Word Word2
------------- ------------ ------------
Shakespeare A Rose
Shakespeare by any
shakespeare other name

.........etc.

I tried using the ID field as a kind of index, starting with 1,3,5,7,9,etc. Then taking Word using ID and Word2 using ID+1.
Unfortunately, it did not work.

Do you have a suggestion or solution."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-25 : 10:57:21


declare @table table
(
	Author 	varchar(100),
	Word 	varchar(100),
	Word2	varchar(100)
)
insert into @table
select	'Shakespeare', 'A', 	'Rose'	union all
select	'Shakespeare', 'by', 	'any'	union all
select	'shakespeare', 'other', 'name'

declare @csv	varchar(4000)

select	@csv = isnull(@csv, '') + Word + ',' + Word2 + ','
from	@table

select	@csv = left(@csv, len(@csv) - 1)
select	@csv
-- A,Rose,by,any,other,name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources