SQL: return many to many in one table

Question

Using a MySQL database I want to store daily measurements of different "channels":

date | channel | value
-----+---------+------
1.1. |     'A' |     1
1.1. |     'B' |     2
1.1. |     'C' |     3
2.1. |     'A' |     4
2.1. |     'C' |     5
3.1. |     'A' |     6
3.1. |     'B' |     7
3.1. |     'C' |     8
4.1. |     'A' |     9
4.1. |     'B' |    10
4.1. |     'C' |    11

Each day the then available channels will be inserted (in the table above the channel 'B' wasn't available on the 2.1.). There will not be more than one measurement per channel per day, i.e. (date,channel) is always unique.

My question:
What is the SQL command to retrieve that data in one table, i.e. a result looking like:

date | A  | B  | C
-----+----+----+---
1.1. |  1 |  2 |  3
2.1. |  4 |  - |  5
3.1. |  6 |  7 |  8
4.1. |  9 | 10 | 11

Optional task: the channel are just a channel ID and a different table maps the ID to the channel name.

(Actually I thought that this is so trivial that there are examples about it everywhere. But either my search engine is broken or I'm trying to do something strange here...)

Note: SQL Cross Tab Function doesn't solve this question as I need the result in separate columns and not aggregated into one.

Search for "crosstab" or "pivot". There are numerous examples on SO and elsewhere. — rd_nielsen, Sep 18 '17 at 20:29
Possible duplicate of [SQL Cross Tab Function](https://stackoverflow.com/questions/9773913/sql-cross-tab-function) — Basil Bourque, Sep 18 '17 at 20:32
Sidenote, if you have a large (or dynamic) number of channel values, these kinds of queries often become quickly unmanageable/unmaintainable as straight queries. The usual solution to that is handling the conversion in code or a reporting package. — Uueerdo, Sep 18 '17 at 20:48

score 2 · Accepted Answer · answered Sep 18 '17 at 22:37

based on your example, you can try this approach

SELECT `date`,
       SUM(CASE WHEN channel_id = 'A' THEN `value` END) 'A',
       SUM(CASE WHEN channel_id = 'B' THEN `value` END) 'B',
       SUM(CASE WHEN channel_id = 'C' THEN `value` END) 'C'       
  FROM t
 GROUP BY `date`
 ORDER BY `date`

Result

date    A   B       C
1.1.    1   2       3
2.1.    4   (null)  5
3.1.    6   7       8
4.1.    9   10      11

SQL: return many to many in one table

1 Answers1