# Group-based Transposition

Question

i need help with this i try to think about resolve this but i cant find how resolve with mysql, I need if the users have record in the week, and show this result with this form:

 user 1 user 2 user 3 week 1 yes no yes week 2 yes yes no week 3 yes no yes week 4 no yes yes week 5 yes no no week 6 yes no yes

I have table user and table record

 TABLES FIELDS user id name record id_user date

There are two tables: one is user table, the other is record table, which records a user activity in a certain date. The task is to compose records expressing whether or not a user has activities (record yes if a user has an activity and no if they haven’t any) and to display them in the desired form.

For this task, one user corresponds to multiple dates and there isn’t any summarization involved. Moreover, dates need to be dynamically converted to week numbers, for which we’ll check whether a user has any activity in this time period. So, in this case, the SQL way of transposing static columns is neither a satisfactory nor an easy solution. Yet there is a convenient way of doing this. That’s the use of SPL. Here’s the SPL script:

 A 1 =connect(“test”) 2 =A1.query(“select   user.name,record.date,record.id_user from user, record where record.id_user=user.id”).run(date=interval@w(“2018-11-1”,date)+1) 3 =A2.group(id_user) 4 =A3.max(date).new(~:week,\${A3.(“\“No\”:”+name).concat@c()}) 5 =A3.run(~.run(A4(date).field(A3.#+1,“Yes”)))

A1: Connect to test database;

A2: Query user table and record table to match user name and record date, and to convert dates to corresponding week numbers;

A3: Group records by id_user field. Each group is a sequence of records made up of name, date and id_user;

A4: Find the biggest week number based on A2, and generate a new table sequence consisting of week column and user name columns. User name columns are dynamically generated according to A3’s groups. The initial values in all user name columns are set as “No”.

A5: Calculate the corresponding week number in A4 for A3’s each record in each group. The result is the nth record in A4, that is A4(n). Then replace the value in this record of the corresponding user in A3 with “Yes”.

A4’s result is what we want.

A2

A3

A4

A4’ result: