Conversion Fail, Multiple Values

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)

Conversion Fail, Multiple Values

Author

Topic

bomerso
Starting Member

2 Posts

Posted - 2013-03-19 : 09:04:06

I am very new to writing views in SQL, so I've quickly run into an problem that I can't figure out.

I needed to convert a field from type varchar to type int so that I could link it to another table in Crystal Reports. Here's my view:

Create view CR_BillingFilterDataINT as

Select
code
,data_type
,convert(int,data) as DATA
From
billing_filter_data

I found that when the DATA field only contains one value, it works fine. But when DATA has multiple values (example 10, 12, 14) it fails to make the conversion. If I understand it correctly, my view is creating this:

Code / DataType / DATA
1 / 1 / 10,12,14

When I need it to create this:

Code / DataType / DATA
1 / 1 / 10
1 / 1 / 12
1 / 1 / 14

Any help would be appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-19 : 09:36:33

comma separated values in a varchar column is a mess...

you can use the seach field in the upper right corner to get some ideas on how to handle that i.e. splitting the data...

Too old to Rock'n'Roll too young to die.

bomerso
Starting Member

2 Posts

Posted - 2013-03-19 : 13:46:28

I have looked through several posts, found some great information (http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows and http://www.sqlexamples.info/SQL/adv_tally10k.htm ), and concluded that this will take more than than I can commit at the moment. Thanks anyway :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources