CSV to Multiple Columns - 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 2005 Forums

Transact-SQL (2005)

CSV to Multiple Columns

Author

Topic

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2009-01-06 : 18:14:58

I have a column with comma separated values (eg. 23,455,34). I would like to separate these values into multiple columns when I do my select from the table. What is the way to do this?

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-06 : 18:55:43

Honestly rather than try to write a complex query that steps through and parses out each number based on the comma delimiter I think it would be easier to export the data to a flatfile and re-import it into a new table using the commas as delimiters for columns.

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2009-01-06 : 19:39:27

I was looking for something along the lines of a select into a temp table or something but without any recursion or looping. Any suggestions anyone?

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-01-07 : 00:14:47

Hi Dear,

if the column has only three values seperated then i hope we can achieve this.

I have the table name A like this.

Id Name
1 11,222,5222
2 1,22,522

Query to get the rows:

SELECT SUBSTRING(NAME,0,CHARINDEX(',',NAME))
,SUBSTRING(NAME,CHARINDEX(',',NAME)+1,(LEN(NAME)-(CHARINDEX(',',REVERSE(NAME))))-CHARINDEX(',',NAME))
,RIGHT(NAME,CHARINDEX(',',REVERSE(NAME))-1)
FROM A

i got the output as follows

column1 column2 column3
11 222 5222
1 22 522

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2009-01-08 : 14:15:44

That was an example. I column data can have any number of CSV values (upto 7 atleast). I wrote an external function which returns to me the number of "," and I do my parsing based on that. Let me know if anyone needs that function.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-08 : 14:34:00

quote:
Originally posted by DeveloperIQ

I have a column with comma separated values (eg. 23,455,34). I would like to separate these values into multiple columns when I do my select from the table. What is the way to do this?

go and curse the person who designed it this way

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-08 : 23:03:46

Hi try this

declare @reports table(report_name varchar(600),report_audience varchar(600) )
insert into @reports
select 'XYZreport', 'Finance,Admin,Management' union all
select 'ABCreport', 'Finance,Management'

SELECT s.report_name,
SUBSTRING(s.report_audience, v.Number - 1,
COALESCE(NULLIF(CHARINDEX(',', s.report_audience, v.Number), 0), LEN(s.report_audience) + 1) - v.Number + 1) AS value
FROM @reports AS s
INNER JOIN master..spt_values AS v ON v.Type = 'p'
WHERE SUBSTRING(',_' + s.report_audience, v.Number, 1) = ','

Jai Krishna

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-09 : 01:06:27

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65290

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources