Sometimes it is required to get query output in tabular form based on cross-tab format. There may be multiple reasons for such output like direct display to front-end, export to file or may be simple analysis. As such sql server provides PIVOT feature to accomplish this task.
Consider a case where shop keeper wants to know what were the sales of each individual item of the shop based on each individual sale invoice. Below is output of how pivot feature can give the desired result:
Consider a case where shop keeper wants to know what were the sales of each individual item of the shop based on each individual sale invoice. Below is output of how pivot feature can give the desired result:
This is achieved with following query:
SELECT row_number() OVER (ORDER BY SalesInvoiceCode) Ser , *
FROM
(
select
sim.SalesInvoiceCode,
case
when a.ID = 1935 then isnull(c.[Description],a.Item) else a.Item
end as [Item],
sum(isnull(c.Rate, a.SaleRate) * isnull(c.Quantity,0)) as Sale
from ERP_SCM_INV_ITEM a
inner join ERP_MASTER_Setup_ItemNature b on b.ItemNatureID_PK = a.ItemNatureID_FK and b.ItemNature = 'posmenu'
inner join ERP_FMS_SalesInvoice_Detail c on c.ItemID_FK=a.ID
inner join ERP_FMS_SalesInvoice_Master sim on sim.SalesInvoiceID_PK = c.SalesInvoiceID_FK
inner join POS_OrderPayment pop on pop.InvoiceId = sim.SalesInvoiceID_PK
inner join ERP_FMS_SalesInvoice_Types sit on sit.SalesInvoiceTypeID_PK = pop.DiningType
where sim.SalesInvoiceCode like '%pos%'
and cast(sim.CreatedDate as date) = '2020-01-13'
and sim.TransactionID_FK > 0 and (sim.closed = 0 and sim.IsCancel is NULL)
and pop.FK_POS_LocationId = '1'
group by
case
when a.ID = 1935 then isnull(c.[Description],a.Item) else a.Item
end ,
sim.SalesInvoiceCode
) t
pivot(
sum(Sale)
for Item in ([Barbosa Cake],[Coffee],[Delivery Charges],[Sitting Charges]
)
)
AS pivot_table
union
SELECT NULL as Ser,* FROM
(
select
'Total' as SalesInvoiceCode,
case
when a.ID = 1935 then isnull(c.[Description],a.Item) else a.Item
end as [Item],
sum(isnull(c.Rate, a.SaleRate) * isnull(c.Quantity,0)) as Sale
from ERP_SCM_INV_ITEM a
inner join ERP_MASTER_Setup_ItemNature b on b.ItemNatureID_PK = a.ItemNatureID_FK and b.ItemNature = 'posmenu'
inner join ERP_FMS_SalesInvoice_Detail c on c.ItemID_FK=a.ID
inner join ERP_FMS_SalesInvoice_Master sim on sim.SalesInvoiceID_PK = c.SalesInvoiceID_FK
inner join POS_OrderPayment pop on pop.InvoiceId = sim.SalesInvoiceID_PK
inner join ERP_FMS_SalesInvoice_Types sit on sit.SalesInvoiceTypeID_PK = pop.DiningType
where sim.SalesInvoiceCode like '%pos%'
and cast(sim.CreatedDate as date) = '2020-01-13'
and sim.TransactionID_FK > 0 and (sim.closed = 0 and sim.IsCancel is NULL)
and pop.FK_POS_LocationId = '1'
group by
case
when a.ID = 1935 then isnull(c.[Description],a.Item) else a.Item
end
)
t2
pivot(
sum(Sale)
for Item in ([Barbosa Cake],[Coffee],[Delivery Charges],[Sitting Charges]
)
)
AS pivot_table2
order by SalesInvoiceCode
