SQL multiple rows in single row

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

Use a union

SELECT 
client, max(availability1) AS availability1, max(availability2) AS availability2
FROM 
(
SELECT
client
 ,availability AS availability1
 ,0 AS availability2
FROM historys hist
WHERE id = (select max(id) from historys where client = hist.client and stock = 1)
    UNION ALL
SELECT
client
 ,0 AS availability1
 ,availability AS availability2
FROM historys hist2
WHERE id = (select max(id) from historys where client = hist2.client and stock = 2)
) a
GROUP by client
ORDER by client

The NULL you have is because your selecting data from lines of historys that cannot contain the availability for stock = 1 and stock = 2 at the same time.

You can bypass that using historys both times like above :

In the subrequest (as d) we get max dates by client, then we join history two times to get both availability in a row.

select d.client,
       h1.availability availability1,
       h2.availability availability2
from
(
  select client,
         max(case when stock = 1 then date end) d1,
         max(case when stock = 2 then date end) d2
  from historys
  group by client
) d
join historys h1 on (h1.stock = 1
                     and h1.date = d.d1
                     and h1.client = d.client)
join historys h2 on (h2.stock = 2
                     and h2.date = d.d2
                     and h2.client = d.client)

You can find an SQLFiddle here : http://sqlfiddle.com/#!9/d1ea4/5

Thanks

This may help you :

 SELECT T.client client ,
    SUM(CASE WHEN T.Stock = 1 THEN T.availability
        END) availability1 ,
    SUM(CASE WHEN T.Stock = 2 THEN T.availability
        END) availability2
 FROM   historys T
    INNER JOIN ( SELECT MAX(date) Max_Date ,
                        stock
                 FROM   historys
                 GROUP BY stock
               ) T1 ON T1.Max_Date = T.date
                       AND T1.stock = T.stock
  GROUP BY T.client

Depending on your MySQL version, you should be able to do this with a window function & a common table expression:

-- Build derived src table with "latest date" rows
WITH src AS (
    SELECT id, client, stock, material, quantity, availability, date
    FROM (
        SELECT 
            id, client, stock, material, quantity, availability, date,
            ROW_NUMBER() OVER(PARTITION BY client, stock ORDER BY date DESC) ClientStockRank -- For each (client, stock) pair, rank each row by date
        FROM historys a
    ) src
    WHERE ClientStockRank = 1 -- Only get rows with "latest date"
)
SELECT 
    client,
    MAX(CASE WHEN stock = 1 THEN availability ELSE NULL) END AS Availability1, -- Get "max" value for "stock 1"
    MAX(CASE WHEN stock = 2 THEN availability ELSE NULL) END AS Availability2 -- Get "max" value for "stock 2"
FROM src
GROUP BY client

I think you need MySQL 8+. If not, there should be other ways to do it too. Let me know if that works.

Update
Actually, this should also suffice:

SELECT DISTINCT
    client,
    MAX(CASE WHEN stock = 1 THEN availability ELSE NULL) OVER(PARTITION BY client ORDER BY date DESC) AS Availability1,
    MAX(CASE WHEN stock = 2 THEN availability ELSE NULL) OVER(PARTITION BY client ORDER BY date DESC) AS availability2
FROM historys