Break down data stored in one field to several columns.

Hi:
My table is tblAllowance, has two columns.
persNumber which is the foreign key to employee table.
allowanceData which is a flat column , The data stored in this column this way: one character is for allowance code, the next 4 characters are for the allowance amount, and so on up tell 5 repeats.
I need to display the data according to allowance code
persNumber   allowanceCode1  allowanceAmount 1 allowanceCode2  allowanceAmount 2 .. and so on.
Please help.
my data like this
Mohammad Alsolaimanapplication programmerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MishaProgrammerCommented:
I edit this commet: replace null on '', like your example
Try this code:
SELECT Table11.number as persNumber,            CASE 
                  WHEN SUBSTRING(Table11.data,1,1) = '1'
                     THEN 1
                  ELSE ''
             END AS  allowanceCode1,
			        CASE 
                  WHEN SUBSTRING(Table11.data,1,1) = '1'
                     THEN SUBSTRING(Table11.data,2,3)
                  ELSE ''
             END AS allowanceAmount1,
			   CASE 
                   WHEN SUBSTRING(Table11.data,1,1) = '2' or SUBSTRING(Table11.data,5,1) = '2'
                     THEN 2
                  ELSE ''
             END AS allowanceCode2,


			        CASE 
                  WHEN SUBSTRING(Table11.data,1,1) = '2' or SUBSTRING(Table11.data,5,1) = '2'
					then case WHEN SUBSTRING(Table11.data,1,1) = '2'
						then SUBSTRING(Table11.data,2,3)
							else SUBSTRING(Table11.data,6,3)
							end
                  ELSE ''
             END AS allowanceAmount2,

			  CASE 
                  WHEN SUBSTRING(Table11.data,1,1) = '3' or SUBSTRING(Table11.data,5,1) = '3' or  SUBSTRING(Table11.data,9,1) = '3'
                     THEN 3
                  ELSE ''
             END AS allowanceCode2,
			         CASE 
                  WHEN   SUBSTRING(Table11.data,1,1) = '3' or SUBSTRING(Table11.data,5,1) = '3' or SUBSTRING(Table11.data,9,1) = '3'
				   then case WHEN SUBSTRING(Table11.data,1,1) = '3'
						then SUBSTRING(Table11.data,2,3)
						else case WHEN SUBSTRING(Table11.data,5,1) = '3'
						then SUBSTRING(Table11.data,6,3)
						else case WHEN SUBSTRING(Table11.data,9,1) = '3'
						then SUBSTRING(Table11.data,10,3)
						END
						END
						END
						else ''
             END AS allowanceAmount3

FROM Table11

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mohammad Alsolaimanapplication programmerAuthor Commented:
thank u very much
0
MishaProgrammerCommented:
Glag to help you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.