sparse matrix - MySQL (SQL in general) limiting columns -

without correct phrase it's hard describe this, give example. consider following data:

cola     colb      colc      cold      cole ------------------------------------------------ hello                                                    can                                                                            please?                  columns   pop'd 

as can see, many columns empty. in end, following output:

col1     col2      col3      col4      col5 ------------------------------------------------ hello          can       please?                  columns   pop'd 

basically, data shifted left. thought of using case when statement, gets pretty complex since it's 7 columns (not 5 outlined in example above).

what efficient way result. name of resultant columns doesn't matter, renamed in case.

treating nothing more thought exercise, because seems best left better structure, or done @ application level - can accomplish want single, horrendously ugly query, this:

select substring_index(substring_index(concat (                 trim(leading '#@' trim(trailing '#@' replace(replace(replace(concat_ws('#@', cola, colb, colc, cold, cole), '#@#@', '#@~!'), '~!#@', ''), '~!', ''))),                 '#@'                 ), '#@', 1), '#@', - 1) col1,     substring_index(substring_index(concat (                 trim(leading '#@' trim(trailing '#@' replace(replace(replace(concat_ws('#@', cola, colb, colc, cold, cole), '#@#@', '#@~!'), '~!#@', ''), '~!', ''))),                 '#@'                 ), '#@', 2), '#@', - 1) col2,     substring_index(substring_index(concat (                 trim(leading '#@' trim(trailing '#@' replace(replace(replace(concat_ws('#@', cola, colb, colc, cold, cole), '#@#@', '#@~!'), '~!#@', ''), '~!', ''))),                 '#@'                 ), '#@', 3), '#@', - 1) col3,     substring_index(substring_index(concat (                 trim(leading '#@' trim(trailing '#@' replace(replace(replace(concat_ws('#@', cola, colb, colc, cold, cole), '#@#@', '#@~!'), '~!#@', ''), '~!', ''))),                 '#@'                 ), '#@', 4), '#@', - 1) col4,     substring_index(substring_index(concat (                 trim(leading '#@' trim(trailing '#@' replace(replace(replace(concat_ws('#@', cola, colb, colc, cold, cole), '#@#@', '#@~!'), '~!#@', ''), '~!', ''))),                 '#@'                 ), '#@', 5), '#@', - 1) col5 yourtable; 

the gist of is, create single delimited string containing columns using concat_ws (i'm using #@ delimiter - can whatever. idea unlikely appear in string). then, though series of replace calls, convert multiple occurrences of delimiter single occurence. 2 calls trim rid of delimiter front , end of string, , add in end of strings concat. can extract word @ each position in string using substring_index. trailing delimiter ensures can empty result if there no more words in string. repeat once every column, incrementing offset inner substring_index call, depending on virtual column you're creating.

demo here

if want extend 7 columns, repeat field twice more, , increment offset each time.