SQL: Creating an aggregated table from a 2-column data source

Question

I have a table within a database, and I want to aggregate the questions and answers within them into a more readable data table. The source table 👇

+-----+----------------+----------------------------+----------------+
| QID | Date Submitted |          Question          |     Answer     |
+-----+----------------+----------------------------+----------------+
|   0 | 08/04/2021     | companyName                | Yaneev         |
|   1 | 08/04/2021     | humanRightPolicy           | George Micheal |
|   2 | 08/04/2021     | accountManagerMobileNumber | 7474236843     |
|   3 | 08/04/2021     | swiftCode                  | AOEU326        |
|   4 | 03/04/2021     | companyName                | Eimbee         |
|   5 | 03/04/2021     | humanRightPolicy           | Revvie George  |
|   6 | 03/04/2021     | accountManagerMobileNumber | 7475123843     |
|   7 | 03/04/2021     | swiftCode                  | AOEU324        |
|   8 | 04/04/2021     | companyName                | Yaneev         |
|   9 | 04/04/2021     | humanRightPolicy           | Mark Fox       |
|  10 | 04/04/2021     | accountManagerMobileNumber | 74742121231    |
|  11 | 04/04/2021     | swiftCode                  | 124eoKK        |
+-----+----------------+----------------------------+----------------+

And what I want the 'destination' table to look like 👇

+--------------+-------------------+----------------------------+-----------+
| Company Name | humanRightsPolicy | accountManagerMobileNumber | swiftCode |
+--------------+-------------------+----------------------------+-----------+
| Yaneev       | George Micheal    |                 7474236843 | AOEU326   |
| Eimbee       | Revvie George     |                 7475123843 | AOEU324   |
+--------------+-------------------+----------------------------+-----------+

I'd also like to know what kind of operation this is, as I feel that it's something that isn't new in any way.

Answer 1

SQL table represent unordered sets. Let me assume that you have an ordering column. With such a column, you can assign a group to each set of rows for the same company and then use conditional aggregation:

select max(case when question = 'Company Name' then answer end) as company_name,
       max(case when question = 'Q1' then answer end) as q1,
       max(case when question = 'Q2' then answer end) as q2,
       max(case when question = 'Q3' then answer end) as q3       
from (select t.*,
             sum(case when question = 'Company Name' then 1 else 0 end) over (order by <ordering col>) as grp
      from t
     ) t
group by grp;

Note that this answers the question that you asked, where all the questions are known -- so the result columns are all known. If you don't know the questions in advance, you need to use dynamic SQL (i.e. construct the query as a string and then execute it). How to do that depends entirely on the database you are using.

EDIT:

In older versions of MySQL, you can use a subquery:

select max(case when question = 'Company Name' then answer end) as company_name,
       max(case when question = 'Q1' then answer end) as q1,
       max(case when question = 'Q2' then answer end) as q2,
       max(case when question = 'Q3' then answer end) as q3       
from (select t.*,
             (select count(*)
              from t t2
              where t2.question = 'Company Name' and
                    t2.<ordering col> <= t.<ordering col>
             ) as grp
      from t
     ) t
group by grp;