Calculate every items’ quantity for each transaction type

declare @ItemId as nvarchar(20)
set @ItemId = null    -- null-->all

select  ItemId,[0] as [Sales Order], [2] as [Production], [3] as [Purchase Order]
from 
(
select ItemId, TransType, Qty
from INVENTTRANS
where TRANSTYPE in (0,2,3)
and ItemId = coalesce(@ItemId, ItemId)    -- if no specified itemid, then query all itemid
) p
PIVOT
(
    Sum(Qty)
    for TransType in
    (
        [0], [2], [3]
    ) 

) as pvt
order by pvt.ITEMID asc

Also, we should add one index for this query, otherwise, it will be reduce the performance

 

/*
Missing Index Details from SQLQuery3.sql - UPZONEAX.ContosoBase (AX2009\administrator (68))
The Query Processor estimates that implementing the following index could improve the query cost by 93.5793%.
*/

USE [ContosoBase]
GO
CREATE NONCLUSTERED INDEX [Idx_TransType]
ON [dbo].[INVENTTRANS] ([TRANSTYPE])
INCLUDE ([ITEMID],[QTY])
GO

Here is a partial result set:

 

ItemId	Sales Order	Production	Purchase Order
1000	-27	NULL	99
1001	-5453	NULL	3751
1003	-2142	NULL	1902
1005	-715	NULL	1720
1101	-2944	2922	10
1102	-1470	1474	NULL
1103	-882	NULL	880
1107	-2050	2028	NULL
1109	-1110	1086	300
1151	-2576	2912	NULL