It's a bad idea because it messes up your data model. You should keep the data as it is and use a pivot table chart object to show it in the format that table nr2 has.
But, if you insist....
load ID, Q1,if(previous(ID)=ID,peek('Ctr')+1,1) as Ctr INLINE [
load 1 as ID AutoGenerate 1;
for i = 1 to FieldValueCount('Ctr')
load ID, Q1 as 'Q1.$(i)'
where Ctr = $(i);
drop table Input;