#Product Transaction Pivot Report
Create Temporary Tables for (User , Product & Product Transaction)
--# Check if table is available or not --
IF (OBJECT_ID('tempdb..##Tbl_User') IS NOT NULL)
BEGIN
DROP TABLE ##Tbl_User
END
-- we have created finalresult table to calculate final result report
IF (OBJECT_ID('tempdb..##Tbl_FinalResult') IS NOT NULL)
BEGIN
DROP TABLE ##Tbl_FinalResult
END
CREATE TABLE ##Tbl_User
(
UID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
UNAME VARCHAR(100)
)
-- Insert Data into user table
INSERT ##Tbl_User values ('Saee P'),('Swaraj P'),('Sharayu P'),('Atul P'),('Pravin P'),('Ninad P')
--# Check if table is available or not --
IF (OBJECT_ID('tempdb..##Tbl_Product') IS NOT NULL)
BEGIN
DROP TABLE ##Tbl_Product
END
CREATE TABLE ##Tbl_Product
(
PID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
PNAME VARCHAR(100),
PCOST DECIMAL(18,2),
PQUNTITY INT
)
-- Insert Data into Product table
INSERT ##Tbl_Product values ('Shoes',1200,12),('Laptop',87000,17),('Hard disk',5000,10),('Cricket Bat',2000,14),
('Mobiles',30000,100),('Android TV LG',70000,20),('Solar',34251,20),('MS SQL 2017 Books',7500,100)
--# Product Transaction
--# Check if table is available or not --
IF (OBJECT_ID('tempdb..##Tbl_ProductTransaction') IS NOT NULL)
BEGIN
DROP TABLE ##Tbl_ProductTransaction
END
CREATE TABLE ##Tbl_ProductTransaction
(
PTID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
UID INT ,
PID INT
)
-- Insert Data into Product Transaction table
INSERT ##Tbl_ProductTransaction VALUES
(1,2),(2,2),(1,1),(2,1),(3,1),(3,3),(2,4),(1,2),(2,3),(1,5),(3,5),(2,5),
(1,5),(3,5),(1,6),(2,6),(3,6),(4,6),(1,6),(5,5),(5,5),(5,7),(1,7),(5,7),
(1,7),(5,7),(1,7),(5,7),(1,7),(5,7),(1,7),(1,6),(2,6),(3,6),(4,6),(1,6),(5,5),
(1,2),(2,2),(1,1),(2,1),(3,1),(3,3),(2,4),(1,2),(2,3),(1,5),(3,5),(2,5),
(1,5),(3,5),(1,6),(2,6),(3,6),(4,6),(1,6),(5,5),(5,5),(5,7),(1,7)--,(5,7),
,(1,7),(5,7),(1,7),(5,7),(1,7),(5,7),(1,7),(1,6),(2,6),(3,6),(4,6),(1,6),(5,5),
(4,4),(4,4),(4,2),(4,8)
-- declare variables to calculate sum amount
-- declare variable for comma separated products
Declare @AllProducts nvarchar(2000)
SET @AllProducts=(
SELECT STRING_AGG(QUOTENAME(PNAME),',') FROM ##Tbl_Product
)
declare @col nvarchar(2000)
SET @col =
(
SELECT STRING_AGG(CONCAT('SUM(','ISNULL(',QUOTENAME(PNAME),',0)',')'),'+') FROM ##Tbl_Product
)
--print @col
Declare @AllProductsListFormat nvarchar(2000)
-- Adding INR rupees format for prodcut Cost and runtime amount columns
SET @AllProductsListFormat=(
SELECT STRING_AGG(CONCAT('FORMAT(',QUOTENAME(PNAME),',''C'',''en-IN'')',QUOTENAME(PNAME)),',') FROM ##Tbl_Product
)
Declare @AllProductsListFooterFormat nvarchar(2000)
--SET @AllProductsListFooterFormat= CONCAT('FORMAT(',@col,',''#,0.00'')')
SET @AllProductsListFooterFormat= CONCAT('FORMAT(',@col,',''C'',''en-IN'')')
--SELECT STRING_AGG(CONCAT('FORMAT(SUM(ISNULL(',QUOTENAME(PNAME),',0))',',''#,0.00'')',QUOTENAME(PNAME)),',') FROM ##Tbl_Product
Declare @AllProductsListFormatTop nvarchar(2000)
SET @AllProductsListFormatTop=(
SELECT STRING_AGG(CONCAT('FORMAT(SUM(ISNULL(',QUOTENAME(PNAME),',0))',',''C'',''en-IN'')',QUOTENAME(PNAME)),',') FROM ##Tbl_Product
)
PRINT @AllProductsListFooterFormat
print @AllProductsListFormatTop
declare @colwithzeroifnull nvarchar(2000)
SET @colwithzeroifnull =
(
SELECT STRING_AGG(CONCAT('ISNULL(',QUOTENAME(PNAME),',0)',QUOTENAME(PNAME)),',') FROM ##Tbl_Product
)
print @colwithzeroifnull
declare @SqlAll nvarchar(max)
-- we use dynamic query, if any new product or transaction added then automatically that newly added record also included in Report
SET @SqlAll='
SELECT * INTO ##Tbl_FinalResult FROM (
Select UID,UNAME,'+@colwithzeroifnull+','+@col+' AS ''<<<<=Vertical Total=>>>>'' From (
SELECT U.UID,U.UNAME,P.PNAME,iSNULL(P.PCOST,0) PCOST FROM ##Tbl_User U LEFT JOIN ##Tbl_ProductTransaction PT
ON PT.UID=U.UID
LEFT JOIN ##Tbl_Product P ON P.PID=PT.PID
)
T
pivot
(
SUM(T.PCOST) for T.PName IN
(
'+@AllProducts+'
)
) as Pivot_TAble
group by UID,UNAME, '+REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@col,'sum(',''),')+',','),')',''),'ISNULL(',''),',0','')+'
)q
SELECT * FROM
(
Select UID,UNAME,'+@AllProductsListFormat+','+@AllProductsListFooterFormat+' As ''<<==Vertical Total==>>'' from ##Tbl_FinalResult
group by UID,UNAME, '+REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@col,'sum(',''),')+',','),')',''),'ISNULL(',''),',0','')+'
)QX
union
Select (IDENT_CURRENT(''##Tbl_User'')+1) UID,
''=>Horizontal Total=>'','+@AllProductsListFormatTop+','+@AllProductsListFooterFormat+'
FROM ##Tbl_FinalResult
'
PRINT @SqlAll
EXEC SP_EXECUTESQL @SqlAll
HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
