SQL query selection riddle

Question

I have a sql query riddle. From a temp table (#tmp) where I managed to retrieve data from DB, i have to reorder the selection to meet the need of a class that is expecting data. Tables from example are simplified enough just to get the idea what is needed. From #tmp, there is column Person, and the second column named Classes contains classId which each person can attend. There are max 13 classes that exist. Columns FieldOne and FieldTwo are the columns that store needed data for each person and each class that person attends.

#tmp

Person          Classes         FieldOne            FieldTwo
----------      ----------      ----------          ----------
1               1               90.00               30.00   
1               2               80.00               30.00   
1               3               70.00               30.00   
2               1               90.00               30.00   
2               2               80.00               30.00   
3               1               90.00               30.00   
3               2               80.00               30.00   
3               3               70.00               30.00   
3               4               60.00               30.00   
...             ...             ...                 ...

I need to select from #tmp table in a way to get only one row for each person, with all 13 classes in that row. For each class there are two columns needed FieldOne and FieldTwo, and where there is no data (eg person 1 does not attend class number 4 in #tmp table), there should be stored 0.00. There can be n persons in list, but only 13 classes they can attend. One row for each person must have personId, values for FieldOne and FieldTwo for all 13 classes, and some other data joined by person id from other tables.

needed selection

Person          ClassID_1-      ClassID_1-      ClassID_2-      ClassID_2-      ClassID_3-      ClassID_3-      ClassID_4-      ClassID_4-      ...
                FieldOne        FieldTwo        FieldOne        FieldTwo        FieldOne        FieldTwo        FieldOne        FieldTwo        ...
----------      ----------      ----------      ----------      ----------      ----------      ----------      ----------      ----------      ...
1               90.00           30.00           80.00           30.00           70.00           30.00           0.00            0.00            ...
2               90.00           30.00           80.00           30.00           0.00            0.00            0.00            0.00            ...
3               90.00           30.00           80.00           30.00           70.00           30.00           60.00           30.00           ...
4...

What is a best approach to achieve that what is needed? If there is smoother way that needs to modify class that is expecting data, it can be done as well, but I prefer it to stay as is.

Have you read and tried https://stackoverflow.com/questions/1241178/mysql-rows-to-columns ? — mjwills, Oct 05 '19 at 12:43

score 0 · Answer 1 · answered Oct 05 '19 at 12:40

Use aggregation:

select person,
       max(case when classid = 1 then field1 end) as field1_1,
       max(case when classid = 1 then field2 end) as field2_1,
       max(case when classid = 2 then field1 end) as field1_2,
       max(case when classid = 2 then field2 end) as field2_2,
       max(case when classid = 3 then field1 end) as field1_3,
       max(case when classid = 3 then field2 end) as field2_3,
       max(case when classid = 4 then field1 end) as field1_4,
       max(case when classid = 4 then field2 end) as field2_4,
       . . . 
from t
group by person;

The only caveat is that you need to know what columns are in the result set -- that is, the number of class ids you want.

If you don't know, then you need to construct the query dynamically. The same idea would hold, but you make the query in a string and then execute it.

SQL query selection riddle

needed selection

1 Answers1