Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

New Column containing contents of several columns into one Expand / Collapse
Author
Message
Posted Friday, June 7, 2013 2:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 8:18 AM
Points: 44, 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!



Post #1461220
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse