I'm creating a query with the function pivot, to show the stock available in all warehouses.
This query is dynamic, to avoid specifying warehouse one by one:
-- Create variable to save warehouse
DECLARE @warehouse nvarchar(1000)
SET @warehouse=''
-- Obtain list of warehouses and assign PIVOT format
SELECT @warehouse = @warehouse + '[' + T.DepId + '],' FROM (
SELECT DISTINCT(SP.DepId) FROM SKDEP SP (NOLOCK)
JOIN SKMOV S (NOLOCK) ON SP.DepId=S.DepId) T
SET @warehouse = LEFT(@warehouse, len(@warehouse)-1)
-- Query
EXEC ('
SELECT * FROM
(
select
s.PrdId as Product,
p.PrdDsc as Description,
CASE WHEN MstEs=''S'' then sum(-MstCntDisp) else sum(MstCntDisp) end as Stock,
s.DepId
FROM SKMOV S
JOIN PRODUC P ON s.PrdId=p.PrdId
WHERE s.mstsal>0 AND s.MstTpo=''S'' and s.PrdId IN(''*TF033'', ''TB1332'')
GROUP BY s.prdid, p.PrdDsc, s.MstES, s.depid
) as t
PIVOT
(
SUM(STOCK)
FOR DEPID IN (' + @warehouse + ')) as P
');
I am trying to replace the null values but I have not succeeded.
The column that shows the available stock is the CASE expression. But I have not succeeded in applying the isnull function to the case expression.