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?
 
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
 
Mohammad Alsolaimanapplication programmerAuthor Commented:
thank u very much
0
 
MishaProgrammerCommented:
Glag to help you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.