CODEWITHSUNDEEP
sqlpostgresqlpivotcrosstab
Ranjith Ramachandra
Ranjith Ramachandra

Reputation: 10764

How to write SQL that can give the following result?

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?

Upvotes: 1

Views: 53

Answers (1)

Paweł Dyl
Paweł Dyl

Reputation: 9143

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

Upvotes: 1

Related Questions