0

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.

enter image description here

Aaron Bertrand
  • 261,961
  • 36
  • 448
  • 471
  • You'd be better off using conditional aggregation rather than the `PIVOT` operator then. – Larnu Dec 15 '21 at 12:44

0 Answers0