get row values to one column recursively (and doing combinations)

I have one input table, it has dates and a fixed number of events. I need to create, from it, the list of events with date of occurrence, and the list of combined events and their occurrence.

Example:

initial table:

CREATE TABLE events (
    date DATE    PRIMARY KEY,
    e1   INTEGER,
    e2   INTEGER,
    e3   INTEGER
);

date       | e1 | e2 | e3 |
--------------------------
2017-02-04 | 2  | 1  | 26 |
2017-02-05 | 14 | 2  | 1  |
2017-02-06 | 1  | 3  |  2 |

Output 1

eventN | total | date1      | date2      |...|  date'N'
--------------------------------------------------------
 01    |  3    | 2017-02-04 | 2017-02-05 |...| 2017-02-06
 02    |  2    | 2017-02-05 | 2017-02-06 |...| (null)
...
 26    | 1     | 2017-02-04 |  (null)    |...| (null)

Output 2

CobineEventN | total | date1      | ... | date'N'
-----------------------------------------------------
  0102       |  2    | 2017-02-05 | ... | 2017-02-06
  0103       |  1    | 2017-02-06 | ... | (null)
  ....
  2526       |  1    | 2017-02-04 | ... | (null)
  ....

Limitations:

• this has to be done in SQLite.
• there is no limit for the dates (i.e. 'n' unique dates).
• the events, are a fixed list of (around)50 ids
• the output will be tables, one for each type of combination.
• the author SQL skills.

After discussing with a teacher, I realized my initial table design was flawed. I revised the table structure to:

CREATE TABLE event (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    date DATE NOT NULL,
    event SMALLINT UNSIGNED NOT NULL
);

This new structure simplifies the data representation:

Step 2: Insert Sample Data

To insert sample data into the table:

INSERT INTO event (date, event) VALUES
('2016-10-05', 1),
('2016-10-07', 1),
('2016-10-05', 2);

Step 3: Create Output 1 - List of Events with Dates of Occurrence

To get the list of events with their dates of occurrence, you can use a CREATE VIEW statement:

CREATE VIEW event_occurrences AS
SELECT event, date FROM event;

CREATE VIEW event_dates AS
SELECT event, COUNT(date) AS total, GROUP_CONCAT(date, ', ') AS dates
FROM event_occurrences
GROUP BY event;

Step 4: Create Output 2 - List of Combined Events and Their Occurrence

To achieve the desired output, I used the following query to combine events occurring on the same date:

SELECT A.event AS nA, B.event AS nB, C.event AS nC, A.date
FROM event AS A, event AS B, event AS C
WHERE A.date = B.date
AND B.date = C.date
AND A.event <> B.event
AND A.event <> C.event
AND B.event <> C.event;

This query produces the necessary combinations:

Although the format isn't exactly what I initially envisioned, the results are correct and meet the project's requirements. This approach also avoids the need for additional columns, simplifying future queries.