IV Available Quantity Report - SQL Pivot Function
Views (5602)
Mahmoud Saadi
32,738
Like
Share
ReportI 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:
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;
Tables included;
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
 |
| Comparison between Two views |
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;
 |
| Quantity on Hand per Item per Site - No Pivot Function |
 |
| 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
This was originally posted here.
*This post is locked for comments