Get data monthly wise in Sql query from single Column

Question

I need to retrieve monthly wise quantity sold from a table.

How can i fetch all monthly wise data from the same column.

SELECT  IH_ITEMNO, SUM(IH_QTY) as July
from tbl_item_history
where IH_DATE LIKE '2022-07%' 
and IH_ITEMNO like '%PG'  and IH_TYPE ='S'
group by IH_ITEMNO 
order By IH_ITEMNO asc

This query return only July data as the input parameter is july. How can I get all the months data without passing input parameter in where clause. Note: - IH_DATE holds all the values of months.

The output would like be ITEMNO, July, Aug, Sep, Oct......

Answer 1

I couldn't find a good example, so try this (you'll need to add the additional columns);

SELECT  IH_ITEMNO, 
SUM(IIF(IH_DATE LIKE '____-06%',IH_QTY,0)) as June,
SUM(IIF(IH_DATE LIKE '____-07%',IH_QTY,0)) as July,
SUM(IIF(IH_DATE LIKE '____-08%',IH_QTY,0)) as August,
SUM(IIF(IH_DATE LIKE '____-09%',IH_QTY,0)) as September
from tbl_item_history
where IH_DATE LIKE '2022%' 
and IH_ITEMNO like '%PG'  and IH_TYPE ='S'
group by IH_ITEMNO 
order By IH_ITEMNO asc

Also be careful of storing dates. What data type is IH_DATE?

Edit

Given that IH_DATE is data type date, it's best to avoid implicit casts (for reasons of performance and defensive programming)

The code below doesn't do any implicit casts and will probably be faster for a large table with an index on IH_DATE

SELECT  IH_ITEMNO, 
SUM(IIF(IH_DATE >= '2022-06-01' AND IH_DATE < '2022-07-01',IH_QTY,0)) as June,
SUM(IIF(IH_DATE >= '2022-07-01' AND IH_DATE < '2022-08-01',IH_QTY,0)) as July
from tbl_item_history
where IH_DATE >= '2022-01-01' AND IH_DATE < '2023-01-01'
and IH_ITEMNO like '%PG'  and IH_TYPE ='S'
group by IH_ITEMNO 
order By IH_ITEMNO asc

EDIT 2

Also be aware that there are many ways to do pivots. In your case the required ourput columns are known and fixed, so this makes it simpler.

There is also some newer pivot syntax here that you might want to use instead but I believe it's just syntactical sugar.