SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


New Column containing contents of several columns into one


New Column containing contents of several columns into one

Author
Message
nulad01
nulad01
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 146
I have an Excel spreadsheet that will be used as an append file to upload to an SQL Server 2008 R2 table.
The Excel spreadsheet has the following columns:
EUnique# StartDate EndDate Location ColumnA ColumnB ColumnC ColumnD
000000001 6/6/2013 1:48:23 PM 6/6/2013 1:49:50 PM LocA CC-H07
000000002 6/6/2013 1:30:11 PM 6/6/2013 1:31:08 PM LocB HM-H08
000000003 6/6/2013 1:29:00 PM 6/6/2013 1:29:47 PM LocC II-H09
000000003 6/5/2013 8:36:54 PM 6/5/2013 8:37:02 PM LocA WW-H10

I am attempting to do this in SSIS using a Drived Column.

I need to make a new single int column based on the results from the above text columns: ColumnA, ColumnB, ColumnC, and ColumnD (I am attempting to do this in SSIS using a Drived Column).
Example: (The NewColumn (int) field's contents should be something like this):

CASE WHEN ColumnA is not null or ColumnA <> '' AND LTRIM(RTRIM(SUBSTRING(ColumnA,FINDSTRING(ColumnA,"-",1) + 1,LEN(ColumnA) - FINDSTRING(ColumnA,"",1)))) = 'CC-H07' THEN 100 (convert the 100 to an int datatype)
WHEN ColumnA is not null or ColumnA <> '' AND LTRIM(RTRIM(SUBSTRING(ColumnA,FINDSTRING(ColumnA,"-",1) + 1,LEN(ColumnA) - FINDSTRING(ColumnA,"",1)))) = 'HM-H08’ THEN 200 (convert the 200 to an int datatype)
WHEN ColumnA is not null or ColumnA <> '' AND LTRIM(RTRIM(SUBSTRING(ColumnA,FINDSTRING(ColumnA,"-",1) + 1,LEN(ColumnA) - FINDSTRING(ColumnA,"",1)))) = 'II-H09’ THEN 300 (convert the 300 to an int datatype)
WHEN ColumnA is not null or ColumnA <> '' AND LTRIM(RTRIM(SUBSTRING(ColumnA,FINDSTRING(ColumnA,"-",1) + 1,LEN(ColumnA) - FINDSTRING(ColumnA,"",1)))) = ' WW-H10 THEN 400 (convert the 400 to an int datatype)
The same continues for:
ColumnB………,
ColumnC………..,
ColumnD……….

If the column has a entry (there will be only one column entry per row), then it will be converted to an int data type depending on what is in the column’s field.

Please advise the best way to accomplish this. CASE is not an option, and not sure how to accomplish this. Your advice will be greatly appreciated. Thanks!



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search