query - 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

query

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2012-03-02 : 18:11:13

Hi All,

I have this table and I want the following columns in the table

HuNum SerialNumber MH GE SF TA DA
A123 04035 100GE 180 SF
A123 04055 12GE 12GE
A567 06664 11TA
A123 09987 31
H567 02234 4DA
P123 04051 51 general 12 task
A456 0998 12 District 17 District
I need to parse the MH field so that 100 appears in the GE field and 180 appears in the SF field, but if both are GE like in second row then I want them to be added 24 and if it is just a single number then I want it in column GE. There are some different values in MH column like general instead of GE and I want to move geneeral to GE column and task to Ta column and district to DA column.

The resulting table will be like

HuNum SerialNumber MH GE SF TA DA
A123 04035 100GE 180 SF 100 180
A123 04055 12GE 12GE 24
A567 06664 11TA 11
A123 09987 31
H567 02234 4DA 4
P123 04055 51 general 12 task 51 12
A456 0992 12 District 17 District 29

any help will be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-02 : 19:55:36

would you mind posting the output in correct format? currently we're having difficulty in understanding which values belong to which column

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

anjali5
Posting Yak Master

121 Posts

Posted - 2012-03-03 : 18:08:23

Below I am posting the formatted question:
I have this table and I want the following columns in the table



HuNum   SN        MH                  GE SF TA DA
A123    04035     100GE 180 SF 
A123    04055     12GE 12GE
A567    06664     11TA 
A123    09987     31 
H567    02234     4DA
P123    04051     51 general 12 task
A456    0998      12 District 17 District

I need to parse the MH field so that 100 appears in the GE field and 180 appears in the SF field, but if both are GE like in second row then I want them to be added 24 and if it is just a single number then I want it in column GE. There are some different values in MH column like general instead of GE and I want to move geneeral to GE column and task to Ta column and district to DA column.

The resulting table will be like




HuNum SN   MH                   GE  SF   TA   DA
A123 04035 100GE 180 SF         100 180
A123 04055 12GE 12GE            24
A567 06664 11TA                          11 
A123 09987 31                   31
H567 02234 4DA                                  4
P123 04055 51 general 12 task   51       12
A456 0992 12 District 17 District                29

any help will be appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-04 : 10:24:03

its a real mess to store data like this. also its not consistent in format too. I think you need to break it up to groups and then apply logic to get data correctly updated

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

anjali5
Posting Yak Master

121 Posts

Posted - 2012-03-05 : 11:35:05

yeah, this data is coming from one of our legacy systems that needs to be cleaned up. I wrote a small query to clean up some data, but still lot of rows are left.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources