Featured Post

Dynamics GP 2018 is now Released

It is officially published that Microsoft Dynamics GP 2018 is available, the download link is provided below: Product download page ...

Sunday, August 18, 2013

IV Available Quantity Report - SQL Pivot Function

I have encountered a case in which a customer requested a certain report to be viewed in a certain format. The essence is that the values stored in Rows should be transferred into Columns, as Shown below:

clip_image001[4]

Fortunately, SQL has the "Pivot" function which serves the need just to the point,
"Pivot Function rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output"
The script below enhances the use of "pivot" function in an Inventory Case Study in order to calculate the Quantity on Hand for each item per Location, as shown below;

image

Quantity on Hand per Item per Site - No Pivot Function

image

Quantity on Hand per Item per Site - Using Pivot Function

The functionality of pivot function is illustrated through an example on Historical Inventory Trial Balance Table SEE30303, as shown in the screenshots above. The script is tested on Fabrikam GP2013, after the HITB reset tool has been implemented to populate SEE30303 table.

-- Tables included;
   
-- SEE30303 | Historical Inventory Trial Balance
USE TWO
SELECT  ITEMNMBR, 
       
ISNULL(WAREHOUSE,0)   AS WAREHOUSE,
           ISNULL(RETURNS,0)  AS RETURNS,
           ISNULL(NORTH,0)  AS NORTH,
           ISNULL(DEPOT,0)   AS DEPOT
           FROM
        ( SELECT *
         
FROM
             
( SELECT [TRXQTYInBase], [ITEMNMBR], [LOCNCODE] 
                FROM [SEE30303] 
                    ) P  
             
PIVOT 
             
(
                 SUM([TRXQTYInBase])
                 FOR [LOCNCODE] IN 
                 ([WAREHOUSE],[RETURNS],[NORTH],[DEPOT])
              ) AS PVT   
        ) AS AB

 

Best Regards,
Mahmoud M. AlSaadi

No comments:

Post a Comment