How to write SQL that can give the following result?

Question

Original table

+------------+---------+-------+-------------+--------+
| CampaignID | Medium  | Month | Impressions | Clicks |
+------------+---------+-------+-------------+--------+
| A          | Google  | Jan   |          15 |      2 |
| B          | Google  | Jan   |          12 |      1 |
| A          | YouTube | Jan   |          11 |      2 |
| B          | YouTube | Jan   |          12 |      4 |
| A          | Google  | Feb   |          15 |      3 |
| B          | Google  | Feb   |          13 |      4 |
| A          | YouTube | Feb   |          12 |      2 |
| B          | YouTube | Feb   |          21 |      5 |
+------------+---------+-------+-------------+--------+

Expected result

+-----------+--------------------+---------------+---------------------+----------------+
| CampainID | Google Impressions | Google Clicks | YouTube Impressions | YouTube Clicks |
+-----------+--------------------+---------------+---------------------+----------------+
| A         |                 30 |             5 |                  23 |              4 |
| B         |                 25 |             4 |                   3 |             39 |
+-----------+--------------------+---------------+---------------------+----------------+

The number of values in the medium need to be generated at run time. This means that the number of columns in the query result are dynamic dependent on the data. What would be the most elegant solution in PostgreSQL?

score 1 · Answer 1 · answered Sep 16 '16 at 09:34

You can query this way:

WITH Src AS
(
SELECT * FROM (VALUES
  ('A', 'Google' , 'Jan', 15, 2),
  ('B', 'Google' , 'Jan', 12, 1),
  ('A', 'YouTube', 'Jan', 11, 2),
  ('B', 'YouTube', 'Jan', 12, 4),
  ('A', 'Google' , 'Feb', 15, 3),
  ('B', 'Google' , 'Feb', 13, 4),
  ('A', 'YouTube', 'Feb', 12, 2),
  ('B', 'YouTube', 'Feb', 21, 5)) T(CampaignID, Medium, Month, Impressions, Clicks)
) --End sample data

SELECT CampaignID,
  SUM(CASE WHEN Medium='Google' THEN Impressions ELSE 0 END) "Google Impessions",
  SUM(CASE WHEN Medium='Google' THEN Clicks ELSE 0 END) "Google Clicks",
  SUM(CASE WHEN Medium='YouTube' THEN Impressions ELSE 0 END) "YouTube Impessions",
  SUM(CASE WHEN Medium='YouTube' THEN Clicks ELSE 0 END) "YouTube Clicks"
FROM Src
GROUP BY CampaignID

But I do not know how many values are there for Medium. Tomorrow I could have another Medium called `Facebook` but the solution I develop now has to be good enough for that case. ie, the new result should have 2 additional columns. — Ranjith Ramachandra, Sep 16 '16 at 10:17

How to write SQL that can give the following result?

1 Answers1