How to parse column data to rows in google sheets with query

Question

I have the follwoing spreadsheet https://docs.google.com/spreadsheets/d/1Mq4REmOqrfQiyCJ8-IIuLDZvzmh_I1lvAqZgOPQCQ74/edit?usp=sharing

I need to re-organize the info as follows.

I have tried using the following:

=query({query(source!A:K,"select A,D");query(source!A:K,"select A,E");query(source!A:K,"select A,F")},"where Col1 is not NULL order by Col1")

which gives me results where semana, horas and pago are all mixed together. See it below.

Any help will be highly appreciated.

Answer 1

You should use labels for setting the right column header and also select the right columns including that extra column representing the week. Here is an example for the first week, for retrieving the rest just append the rest weeks as you did in the example you provided:

=query(source!A:K,"select A,'2020-20',D,E where A is not NULL label A 'Nombre', D 'horas',E'pago','2020-20' 'semana'")

Demonstration:

To query the rest of years you just need to nest the queries in a bigger query and select the right columns of each week. For example, the relevant columns for the week 2020-20 would be A (the one containing the names which is comon for all weeks), D and E as these are the columns containing that week's information. For 2020-21 it would be columns A, F and G and so on. Also, note that you will only need to add the labels on the first query as you only want the headers Nombre,semana,horas and pago on the first row.

Here is an example with the first two weeks:

=query({query(source!A:K,"select A,'2020-20',D,E where A is not NULL label A 'Nombre', D 'horas',E'pago','2020-20' 'semana'");query(source!A:K,"select A,'2020-21',F,G where A is not NULL")})