Select Sold and unsold product from same table in SQL Server for last month

I have Database table and trying to write query to find sold and not sold product list from one table.

Table is Below

Expecting Result

How do i get this result? i am using CTE to create Tamp table and with all services and then do left join but it dose give me only product sold in Feb, but i want all product with no sell too.

Answer

You can cross join the products and the dates, and then bring the table with a left join:

select 
    p.product,
    t.quantity_sold,
    d.yr,
    d.mn
from (select distinct product from mytable) p
cross join (select distinct yr, mn from mytable) d
left join mytable t
    on  t.product = p.product
    and t.yr = d.yr
    and t.mn = d.mn

This puts nulls for rows with no sale – that’s presumably a numeric column so you generally don’t want to write a string like 'Not Sold' into it.

If there is a possibility of duplicate (product, yr, mn), you might want to use outer aggregation:

select 
    p.product,
    sum(t.quantity_sold) quantity_sold,
    d.yr,
    d.mn
from (select distinct product from mytable) p
cross join (select distinct yr, mn from mytable) d
left join mytable t
    on  t.product = p.product
    and t.yr = d.yr
    and t.mn = d.mn
group by p.product, d.yr, d.mn