Text to Columns in SQL - 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 2008 Forums

Transact-SQL (2008)

Text to Columns in SQL

Author

Topic

sqlnovice1
Starting Member

34 Posts

Posted - 2011-08-04 : 05:29:27

Hi

I have a problem at work (a hospital). We have data stored in a DWH that is taken from an overnight snapshot from the live sysytem and resides in a view in SQL server.

I have a column from the live system which contains multiple data items seperated by a '/'.

i need to show this field as seperate columns (up to a max of 10)

Below is an example of my data and also an expected results table.

Any help or advice would be greatly appreciated

--Data I have to work with --

CREATE TABLE PS_TestForOnline
(
crn NVARCHAR (10),
all_codes_in_one_field VARCHAR (500),

);

INSERT INTO PS_TestForOnline
VALUES('11111','W83.1, Endoscopic drilling of lesion of articular cartilage / Y76.7, Arthroscopic approach to joint / W74.2, Reconstruction of intra-articular ligament NEC / Y69.8, Other specified harvest of other tissue' );
INSERT INTO PS_TestForOnline
VALUES('22222','W28.3, Removal of internal fixation from bone NEC / W31.9, Unspecified other autograft of bone / W62.9, Unspecified other primary fusion of other joint' );
INSERT INTO PS_TestForOnline
VALUES('33333','L62.1, Repair of femoral artery NEC' );
INSERT INTO PS_TestForOnline
VALUES('44444','E13.6, Puncture of maxillary antrum / E08.1, Polypectomy of internal nose' );

select * from PS_TestForOnline

drop table PS_TestForOnline

----------------------------------------------------------

--Expected results--

CREATE TABLE PS_TestForOnline_expected_answer
(
crn int,
CODE_POS_1 NVARCHAR (500),
CODE_POS_2 NVARCHAR (500),
CODE_POS_3 NVARCHAR (500),
CODE_POS_4 NVARCHAR (500),
CODE_POS_5 NVARCHAR (500),
CODE_POS_6 NVARCHAR (500),
CODE_POS_7 NVARCHAR (500),
CODE_POS_8 NVARCHAR (500),
CODE_POS_9 NVARCHAR (500),
CODE_POS_10 NVARCHAR (500),
);

INSERT INTO PS_TestForOnline_expected_answer
VALUES('11111','W83.1, Endoscopic drilling of lesion of articular cartilage','Y76.7, Arthroscopic approach to joint','W74.2, Reconstruction of intra-articular ligament NEC','Y69.8, Other specified harvest of other tissue','','','','','','');
INSERT INTO PS_TestForOnline_expected_answer
VALUES('22222','W28.3, Removal of internal fixation from bone NEC','W31.9, Unspecified other autograft of bone','W62.9, Unspecified other primary fusion of other joint','','','','','','','' );
INSERT INTO PS_TestForOnline_expected_answer
VALUES('33333','L62.1, Repair of femoral artery NEC','','','','','','','','','' );
INSERT INTO PS_TestForOnline_expected_answer
VALUES('44444','E13.6, Puncture of maxillary antrum','E08.1, Polypectomy of internal nose','','','','','','','','' );

select * from PS_TestForOnline_expected_answer

drop table PS_TestForOnline_expected_answer

Paul

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-04 : 05:35:36

use fnParseList()
from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 08:59:19

see here for a solution


http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice1
Starting Member

34 Posts

Posted - 2011-08-04 : 09:15:26

Hi

I've had a look at the solutions above. I'm trying to split a field in to columns and not rows. please see my expected results.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 09:18:33

you can use same solutions and get it onto rows. then use PIVOT or cross tab logic to get them back onto different columns as your output shows

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-08-08 : 09:56:49

See if this helps
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