CSV Help!!! - SQL Server Forums

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

Transact-SQL (2000)

CSV Help!!!

Author

Topic

mem
Starting Member

28 Posts

Posted - 2004-10-08 : 22:04:42

Hello,

I'm using a couple of different techniques in order to get a CSV string into two columns. unfortunately, none of them are working ;)

Here's sample ddl, etc...

CREATE TABLE tblBogus
(
bogus_id int PRIMARY KEY,
bogus_codes varchar(4000),
bogus_date datetime
)
go

INSERT tblBogus VALUES (1,'1234;5431;9999','10/08/2004')
INSERT tblBogus VALUES (2,'8888','10/08/2004')
INSERT tblBogus VALUES (3,'7777;1111',''10/06/2004'')
INSERT tblBogus VALUES (4,'3333;1234;5555',''10/02/2004'')
INSERT tblBogus VALUES (5,'2222','10/08/2004')

Ok, I'm using Garth's article 'Using COALESCE to Build Comma-Delimited String' to produce a delimited string from bogus_codes column. But I want to include the bogus_date column as well.

For example,

DECLARE @bogus_codes varchar(4000)

SELECT @bogus = COALESCE(@bogus + ';', '') + bogus_codes
FROM tblBogus

SELECT @bogus_codes

So that should produce this string: 1234;5431;9999;8888;7777;1111;3333;1234;5555;2222

Then I'm using Graz's article 'Using a CSV with an IN sub-select' to produce the table from the csv string.

Which should give me:

IntValue
===========
1234
5431
9999
8888
7777
1111
3333
1234
5555
2222

Now, what I really want is these results:

IntValue | Date
================
1234 | 10/08/2004
5431 | 10/08/2004
9999 | 10/08/2004
8888 | 10/08/2004
7777 | 10/06/2004
1111 | 10/06/2004
3333 | 10/02/2004
1234 | 10/02/2004
5555 | 10/02/2004
2222 | 10/08/2004

Well, that's it. Any help would be appreciated. Thanks in advanced.

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-11 : 01:47:33

if you have recordset (I named it as 'csv') like
IntValue
===========
1234
5431
9999
8888
7777
1111
3333
1234
5555
2222

then the query you want should be like

select IntValue, bogus_date from csv join tblbogus on bogus_codes like ('%'+cast(IntValue as varchar)+'%')

mem
Starting Member

28 Posts

Posted - 2004-10-11 : 13:34:44

Would grouping be necessary? From the look of your query this could produce duplicate rows? Thanks for the reply.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-11 : 14:24:32

Uses a tally table (I have called it numbers):

SELECT 
	NullIf(SubString(';' + bogus_codes + ';' , n , CharIndex(';' , ';' + bogus_codes + ';' , n) - n) , '') AS bogus_code,
	bogus_date
FROM numbers, tblBogus 
WHERE n <= Len(';' + bogus_codes + ';') AND SubString(';' + bogus_codes + ';' , n - 1, 1) = ';'

mem
Starting Member

28 Posts

Posted - 2004-10-11 : 17:29:27

Awesome!!! I read that article to shed some light on the 'tally' table technique. I was able to perform the entire task in one select statement!

Thank God there's a lot of people out there that are smarter than me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources