SQL Query for a PIVOT report
source link: https://answers.sap.com/questions/14032329/sql-query-for-a-pivot-report.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
I have written a query for the query manager below.
DECLARE @ItemCode NVARCHAR(255)
SET @ItemCode = 'MyItemCode'
SELECT
MONTH([DocDate]) AS [Month],
SUM(CASE WHEN YEAR([DocDate]) = 2015 THEN [Quantity] END) AS [2015],
SUM(CASE WHEN YEAR([DocDate]) = 2016 THEN [Quantity] END) AS [2016],
SUM(CASE WHEN YEAR([DocDate]) = 2017 THEN [Quantity] END) AS [2017],
SUM(CASE WHEN YEAR([DocDate]) = 2018 THEN [Quantity] END) AS [2018],
SUM(CASE WHEN YEAR([DocDate]) = 2019 THEN [Quantity] END) AS [2019],
SUM(CASE WHEN YEAR([DocDate]) = 2020 THEN [Quantity] END) AS [2020],
SUM(CASE WHEN YEAR([DocDate]) = 2021 THEN [Quantity] END) AS [2021],
SUM(CASE WHEN YEAR([DocDate]) = 2022 THEN [Quantity] END) AS [2022],
SUM(CASE WHEN YEAR([DocDate]) = 2023 THEN [Quantity] END) AS [2023],
SUM(CASE WHEN YEAR([DocDate]) = 2024 THEN [Quantity] END) AS [2024]
FROM (
SELECT T0.[DocDate], T1.[ItemCode], T1.[Quantity]
FROM OINV AS T0
INNER JOIN INV1 AS T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T1.[ItemCode] LIKE @ItemCode
UNION ALL
SELECT T0.[DocDate], T1.[ItemCode], -T1.[Quantity] AS [Quantity]
FROM ORIN AS T0
INNER JOIN RIN1 AS T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T1.[ItemCode] LIKE @ItemCode
) AS T2
GROUP BY MONTH([DocDate])
ORDER BY [Month];
This query works fine when I hardcode the @ItemCode, however I want to the query manager to get the itemcode from the user and save the itemcode to @ItemCode.
Can someone help please.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK