MySQL pivot: Display values in same row

Question

With the below table:

CREATE TABLE `STOCK_LEVEL`(
  `ID` int(11),
  `Date` date,
  `ProductCode` varchar(50),
  `Quantity` int(10)
)

INSERT INTO `STOCK_LEVEL` (`ID`, `Date`, `ProductCode`, `Quantity`) VALUES
(1, '2018-02-10', 'PROD01', 15),
(2, '2018-02-10', 'PROD02', 90),
(3, '2018-02-14', 'PROD03', 5),
(4, '2018-02-14', 'PROD01', 11);

I would like to see the stock level for a given product on a given day, so I have created a query by product:

SELECT 
    Date,
    (Case when ProductCode = 'PROD01' then Quantity else 0 end) As 'AlphaProduct', 
    (Case when ProductCode = 'PROD02' then Quantity else 0 end) As 'BetaProduct', 
    (Case when ProductCode = 'PROD03' then Quantity else 0 end) As 'GammaProduct'

FROM STOCK_LEVEL

WHERE Date IN( '2018-02-10', '2018-02-14')

Which will give me a result like this:

+------------+--------------+-------------+--------------+
| Date       | AlphaProduct | BetaProduct | GammaProduct |
+------------+--------------+-------------+--------------+
| 2018-02-10 | 15           | 0           | 0            |
+------------+--------------+-------------+--------------+
| 2018-02-10 | 0            | 90          | 0            |
+------------+--------------+-------------+--------------+
| 2018-02-14 | 0            | 0           | 5            |
+------------+--------------+-------------+--------------+
| 2018-02-14 | 11           | 0           | 0            |
+------------+--------------+-------------+--------------+

I am probably missing something basic, but is there a way to have this grouped by date like this: (I can't GROUP BY since there is no aggregate?)

+------------+--------------+-------------+--------------+
| Date       | AlphaProduct | BetaProduct | GammaProduct |
+------------+--------------+-------------+--------------+
| 2018-02-10 | 15           | 90          | 0            |
+------------+--------------+-------------+--------------+
| 2018-02-14 | 11           | 0           | 5            |
+------------+--------------+-------------+--------------+

You need to use `MAX(Case when ProductCode = '[]' then Quantity else 0 end)` AS '[]' and a `GROUP BY date` — Raymond Nijland, Feb 20 '18 at 20:30
@raymond-nijland that is to say unless you were implying to use a subquery — Sirmyself, Feb 20 '18 at 20:41
That will work @Sirmyself https://stackoverflow.com/questions/7674786/mysql-pivot-table without using a subquery — Raymond Nijland, Feb 20 '18 at 20:42
@RaymondNijland ... you have comment the right answer .. post a valid code so we can rate properly — ScaisEdge, Feb 20 '18 at 20:43
@raymond-nijland wow... I actually tried and it didn't work, tried again after your comment and turn's out it worked. — Sirmyself, Feb 20 '18 at 20:46
@scaisEdge i tend not to post answers on duplicated questions.. duplicated question normally get a close vote by me with a duplication link (forgotten this time) — Raymond Nijland, Feb 20 '18 at 20:50
@RaymondNijland your comment is the right answer and for duplicated answer .. is not so easy for new OP find the right answer .. i think you should post your commen as an answer and when you have done it i .. remove my answer .. — ScaisEdge, Feb 20 '18 at 20:51
Sorry if this is a duplicate, I did do my search, I but since the issue is new to me I could not even start to ask the right question :/. Looking at the other question, I don't think I could have answered my question based on that though, since the trick for me here was the fake aggregation. — xedemx, Feb 21 '18 at 00:45

score 3 · Accepted Answer · answered Feb 20 '18 at 20:42

3

Use a a (fake) aggreation function and group by

SELECT 
Date,
max(Case when ProductCode = 'PROD01' then Quantity else 0 end) As 'AlphaProduct', 
max(Case when ProductCode = 'PROD02' then Quantity else 0 end) As 'BetaProduct', 
max(Case when ProductCode = 'PROD03' then Quantity else 0 end) As 'GammaProduct'

FROM STOCK_LEVEL

WHERE Date IN( '2018-02-10', '2018-02-14')
group by Date

answered Feb 20 '18 at 20:42

ScaisEdge

131,976
10
91
107

I'm beginning to wonder : why do people use `CASE` instead of the `IF()` function? – Sirmyself Feb 20 '18 at 20:54
I prefer case because i more powerfull. and more easy to understand .. but in this case is for let the OP more confident with familiar code – ScaisEdge Feb 20 '18 at 20:55
More powerful ok. But for that purpose, isn't it like using a smelting furnace to light a candle? I prefer `IF(ProductCode = 'PROD01', Quantity, 0)` since it is more compact, as fast as using `Case` and easier to read according to me – Sirmyself Feb 20 '18 at 20:58
In this case .. the main concept is the use of fake aggregation function and group for reduce rows .. .. the rest is a personal preference .. for me both are good .. i used the code of the OP more is possible so .. is more easy for the OP to understand and for answering – ScaisEdge Feb 20 '18 at 21:02
granted, I just cringe when I see too complicated code for a purpose. Pretty much like seeing someone rewrite the quick-sort algorithm to sort a 15 elements array. – Sirmyself Feb 20 '18 at 21:04
Anyway .. good coded answer is always good to to see .. if you thinks so you could post you answer with your style code .. should now have understood how to do and will soon erase this answer – ScaisEdge Feb 20 '18 at 21:09
oh no, your answer is perfect, I was just wondering. If I wrote an answer, the only difference would be that. – Sirmyself Feb 20 '18 at 21:11
Often the difference between answers is related to style , clarity, completeness .. two good answers are always worth more than a comment – ScaisEdge Feb 20 '18 at 21:14
two time the same answer though : probably not – Sirmyself Feb 20 '18 at 21:15
Ok ... as you prefer .. – ScaisEdge Feb 20 '18 at 21:15
@Sirmyself Agreed, 'if'might be cleaner. However, I was looking for a solution first ;). Thank you all! – xedemx Feb 21 '18 at 00:49
@scaisEdge Thank you again for the solution, works perfectly for my case. I would add that instead of MAX one should use SUM since, if there are negative values the MAX will not display them. A marginal situation, but maybe someone might benefit from this. (I did need it for my case) – xedemx Feb 23 '18 at 02:22

MySQL pivot: Display values in same row

1 Answers1