Seperating into new 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

General SQL Server Forums

New to SQL Server Programming

Seperating into new columns

Author

Topic

sqln00b9
Starting Member

8 Posts

Posted - 2010-12-03 : 16:51:44

Hello,
I have a table into which I imported data...it is all under one column
COLUMNONE
A|B|C|D|E
1|2|3|4|5

Basically, I need to create 5 columns and data is seperated by |.

So result will be:

COLUMNONE, COLUMNTWO,COLUMNTHREE,COLUMNFOUR,COLUMN5
A,B,C,D,E
1,2,3,4,5

Thanks!!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-03 : 19:17:26

Just re-import the table and specify the fields are delimited by a |..that would be the easiest way.

Poor planning on your part does not constitute an emergency on my part.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 03:28:35

That is the best way suggested by Dataguru
But still you want the query then Use the following -


DECLARE @Sample AS TABLE ( COLUMNONE VARCHAR(200) )

INSERT INTO @Sample
SELECT 'A|B|C|D|E' UNION ALL
SELECT '1|2|3|4|5'

SELECT [Column1],[Column2],[Column3],[Column4],[Column5] FROM 
( 
SELECT *, 'Column' + CONVERT ( VARCHAR(20), ROW_NUMBER() OVER ( PARTITION BY ColumnOne ORDER BY ColumnOne ) ) ColumnNames FROM @Sample T
CROSS APPLY 
(
SELECT strData FROM SplitText( T.ColumnOne, '|' )
) A
) B 
PIVOT( MAX( strData ) FOR ColumnNames IN ([Column1],[Column2],[Column3],[Column4],[Column5]) ) D

Vaibhav T

If I cant go back, I want to go fast...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-07 : 03:01:48

Also refer
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/11/splitting-delimited-data-to-columns-set-based-approach.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources