Transposing SQL Result

Question

Given that Store table contains Revenue and Category column, to find out what is the revenue by category I can do a SQL query similar to something below.

Store table:
+------+---------+----------+
| ID   | Revenue | Category |
+------+---------+----------+
| 1    | 60      | Toy      |
| 2    | 50      | Food     |
| 3    | 40      | Food     |
| 1    | 6       | Drinks   |
| 1    | 5       | Drinks   |
+------+---------+----------+

SELECT SUM(Revenue), Category
FROM Department
GROUP BY Category

I would then get the following result below.

+---------+----------+
| Revenue | Category |
+---------+----------+
| 60      | Toy      |
| 90      | Food     |
| 11      | Drinks   |
+---------+----------+

However, would it be possible to transpose the category column such that I am able to get the following results below?

+-------------+--------------+----------------+
| Toy_Revenue | Food_Revenue | Drinks Revenue |
+-------------+--------------+----------------+
| 60          | 90           | 11             |
+-------------+--------------+----------------+

Consider handling issues of data display in application code — Strawberry, Mar 15 '20 at 13:08
Using MySQL for pivoting in any kind of general way is a notorious pain in the neck. Consider @Strawberry's suggestion. — O. Jones, Mar 15 '20 at 13:29

score 0 · Answer 1 · answered Mar 15 '20 at 13:08

0

You can use conditional aggregation:

SELECT SUM(CASE WHEN Category = 'Toy' THEN Revenue END) as Toy,
       SUM(CASE WHEN Category = 'Food' THEN Revenue END) as Food,
       SUM(CASE WHEN Category = 'Drinks' THEN Revenue END) as Drinks
FROM Department

answered Mar 15 '20 at 13:08

Gordon Linoff

1,242,037
58
646
786

Transposing SQL Result

1 Answers1