Q:

SQL multiple rows in single row

Hi everyone I have this table and I want to show result in single row group by client id and show availability for stock 1 and 2.
Here is my table
id      client  stock   material    quantity    availability    date    
62      56      1       0           0           100             2017-12-16 23:55:01 
63      56      2       0           0           900             2017-12-16 23:55:01 
64      56      1       100         -20         80              2017-12-16 23:55:20 
65      56      1       80          100         180             2017-12-16 23:56:06 
66      56      1       180         200         380             2017-12-16 23:56:21 
67      56      1       380         500         880             2017-12-16 23:58:11 
68      56      1       880         -580        300             2017-12-16 23:58:38 
69      56      2       900         -90         810             2017-12-17 23:59:18 

Outcome I want is get result from last date, group by client id and combine stock 1 and stock 2 to single row
client  availability1   availability2
56      300             810

I try this query
SELECT
    historys.id
    ,(CASE WHEN historys.stock = 1 THEN availability END) AS availability1
    ,(CASE WHEN historys.stock = 2 THEN availability END) AS availability2
FROM historys
GROUP BY historys.client
ORDER by historys.id

The result is
id  availability1   availability2 
56  NULL            810

I will be grateful if someone help me. Thanks.

A:

You need to filter to the right rows before the aggregation.  Here is one method:
SELECT h.client,
       MAX(CASE WHEN h.stock = 1 THEN h.availability END) AS availability1
       MAX(CASE WHEN h.stock = 2 THEN h.availability END) AS availability2
FROM historys h
WHERE h.date = (SELECT MAX(h2.date) FROM historys h2 WHERE h2.stock = h.stock)
GROUP BY h.client

