Dynamic Pivot in SQL with ROW and COLUMN TOTAL

Question

User2101416554 posted

My Table

CREATE TABLE [dbo].[ShoppingDetail](
	[CustomerName] [varchar](256) NOT NULL,
	[ProductCategory] [varchar](256) NOT NULL,
	[TotalAmount] [numeric](18, 2) NOT NULL,
	[Total Value] [numeric](18, 2) NULL,
	[Date] [date] NULL
) ON [PRIMARY]
GO

Table Data

INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Roy Martin', N'Clothing', CAST(2967.00 AS Numeric(18, 2)), CAST(1500.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Roy Martin', N'Accessories', CAST(2458.00 AS Numeric(18, 2)), CAST(2500.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Roy Martin', N'Bikes', CAST(52478.00 AS Numeric(18, 2)), NULL, CAST(N'2019-12-10' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Caitlin C Watson', N'Clothing', CAST(3289.00 AS Numeric(18, 2)), CAST(4000.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Caitlin C Watson', N'Bikes', CAST(75769.00 AS Numeric(18, 2)), CAST(5000.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Clothing', CAST(2875.00 AS Numeric(18, 2)), CAST(7000.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Accessories', CAST(6567.00 AS Numeric(18, 2)), CAST(6500.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Bikes', CAST(46897.00 AS Numeric(18, 2)), CAST(3200.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Clothing', CAST(15783.00 AS Numeric(18, 2)), NULL, CAST(N'2019-12-14' AS Date))

Dynamic Pivot Query for Date column

DECLARE @SQLStatement NVARCHAR(MAX) = N'' --Variable to hold t-sql query
DECLARE @UniqueCustomersToPivot NVARCHAR(MAX) = N'' --Variable to hold unique customers to be used in PIVOT clause
DECLARE @PivotColumnsToSelect NVARCHAR(MAX) = N'' --Variable to hold pivot column names with alias to be used in SELECt clause
--Extarct unique customer names with pivot formattings
SELECT @UniqueCustomersToPivot = @UniqueCustomersToPivot + ', [' + COALESCE( REPLACE(CONVERT(CHAR(11), Date, 106),' ','-'), '') + ']' FROM (SELECT DISTINCT [Date] FROM dbo.ShoppingDetail)DT
SELECT @UniqueCustomersToPivot = LTRIM(STUFF(@UniqueCustomersToPivot, 1, 1, '')) --Remove first comma and space
--Generate column names to be put in SELECT list with NULL handling and aliases also
SELECT @PivotColumnsToSelect = @PivotColumnsToSelect + ', ISNULL([' + COALESCE( REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') , '') + '], 0) AS [' + REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') + ']'
FROM (SELECT DISTINCT [Date] FROM dbo.ShoppingDetail)DT
--Generate dynamic PIVOT query here
SET @SQLStatement =
N'SELECT CustomerName,ProductCategory '
+ @PivotColumnsToSelect +
'
FROM dbo.ShoppingDetail
PIVOT
(
SUM(TotalAmount) FOR
[Date] IN
(' + @UniqueCustomersToPivot + ')
) AS PVT Order By CustomerName
'
--Execute the dynamic t-sql PIVOT query below
EXEC (@SQLStatement)

The above query is working fine ... 

But i need the below

1. I need Row and Column of this Pivot
2. How to add where condition like TotalAmount is not null

Answer 1

User2101416554 posted

My Table

CREATE TABLE [dbo].[ShoppingDetail](
	[CustomerName] [varchar](256) NOT NULL,
	[ProductCategory] [varchar](256) NOT NULL,
	[TotalAmount] [numeric](18, 2) NOT NULL,
	[Total Value] [numeric](18, 2) NULL,
	[Date] [date] NULL
) ON [PRIMARY]
GO

Table Data

INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Roy Martin', N'Clothing', CAST(2967.00 AS Numeric(18, 2)), CAST(1500.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Roy Martin', N'Accessories', CAST(2458.00 AS Numeric(18, 2)), CAST(2500.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Roy Martin', N'Bikes', CAST(52478.00 AS Numeric(18, 2)), NULL, CAST(N'2019-12-10' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Caitlin C Watson', N'Clothing', CAST(3289.00 AS Numeric(18, 2)), CAST(4000.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Caitlin C Watson', N'Bikes', CAST(75769.00 AS Numeric(18, 2)), CAST(5000.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Clothing', CAST(2875.00 AS Numeric(18, 2)), CAST(7000.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Accessories', CAST(6567.00 AS Numeric(18, 2)), CAST(6500.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Bikes', CAST(46897.00 AS Numeric(18, 2)), CAST(3200.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Clothing', CAST(15783.00 AS Numeric(18, 2)), NULL, CAST(N'2019-12-14' AS Date))

Dynamic Pivot Query for Date column

DECLARE @SQLStatement NVARCHAR(MAX) = N'' --Variable to hold t-sql query
DECLARE @UniqueCustomersToPivot NVARCHAR(MAX) = N'' --Variable to hold unique customers to be used in PIVOT clause
DECLARE @PivotColumnsToSelect NVARCHAR(MAX) = N'' --Variable to hold pivot column names with alias to be used in SELECt clause
--Extarct unique customer names with pivot formattings
SELECT @UniqueCustomersToPivot = @UniqueCustomersToPivot + ', [' + COALESCE( REPLACE(CONVERT(CHAR(11), Date, 106),' ','-'), '') + ']' FROM (SELECT DISTINCT [Date] FROM dbo.ShoppingDetail)DT
SELECT @UniqueCustomersToPivot = LTRIM(STUFF(@UniqueCustomersToPivot, 1, 1, '')) --Remove first comma and space
--Generate column names to be put in SELECT list with NULL handling and aliases also
SELECT @PivotColumnsToSelect = @PivotColumnsToSelect + ', ISNULL([' + COALESCE( REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') , '') + '], 0) AS [' + REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') + ']'
FROM (SELECT DISTINCT [Date] FROM dbo.ShoppingDetail)DT
--Generate dynamic PIVOT query here
SET @SQLStatement =
N'SELECT CustomerName,ProductCategory '
+ @PivotColumnsToSelect +
'
FROM dbo.ShoppingDetail
PIVOT
(
SUM(TotalAmount) FOR
[Date] IN
(' + @UniqueCustomersToPivot + ')
) AS PVT Order By CustomerName
'
--Execute the dynamic t-sql PIVOT query below
EXEC (@SQLStatement)

The above query is working fine ... 

But i need the below

1. I need Row and Column of this Pivot
2. How to add where condition like TotalAmount is not null

How to add more column is select query with the given below link. Like personname,pincode etc

I got the solution from this link

https://www.aspforums.net/Threads/165889/Calculate-Row-Total-and-Column-Total-using-dynamic-Pivot-query-in-SQL-Server/

Answer 2

User77042963 posted

CREATE TABLE [dbo].[ShoppingDetail](
	[CustomerName] [varchar](256) NOT NULL,
	[ProductCategory] [varchar](256) NOT NULL,
	[TotalAmount] [numeric](18, 2) NOT NULL,
	[Date] [date] NULL
)

INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Roy Martin', N'Clothing', CAST(2967.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
,(N'Roy Martin', N'Accessories', CAST(2458.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
, (N'Roy Martin', N'Bikes', CAST(52478.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
, (N'Caitlin C Watson', N'Clothing', CAST(3289.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
, (N'Caitlin C Watson', N'Bikes', CAST(75769.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
,(N'Taylor Torres', N'Clothing', CAST(2875.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
,(N'Taylor Torres', N'Accessories', CAST(6567.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
,(N'Taylor Torres', N'Bikes', CAST(46897.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
, (N'Taylor Torres', N'Food Products', CAST(15783.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))

declare @ColumnHeaders NVARCHAR(4000) ;
declare @ColumnHeaders2 NVARCHAR(4000) ;
declare  @sql NVARCHAR(4000);
declare @Param int=7  --day
-- --===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
WHere n<= @Param ) ---Control the total columns list
,dates as (
Select n, DATEADD(day, -n+1, (Select max([date]) from ShoppingDetail) ) dt  
from(Select n from nums) D(n)
)
 
Select @ColumnHeaders = STUFF( (SELECT ',' + 'SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end) as ' + Quotename(Convert(char(10),dt,120),'[')  + char(10)+char(13)
FROM  dates   
Order by n desc
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')   
,
@ColumnHeaders2 = STUFF( (SELECT '+' + 'isnull(SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end),0) '   + char(10)+char(13)
FROM  dates   
Order by n desc
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')   
 
 From dates t1


  
SET @sql = N'Select  Case when [CustomerName] is null then ''Total'' else [CustomerName] end [CustomerName], '
+ @ColumnHeaders + ','+@ColumnHeaders2+' as [total] '
+' from [ShoppingDetail]
Group by grouping sets(CustomerName,())  '
  
--print @sql
 EXEC sp_executesql @sql;
     

drop table [ShoppingDetail]

Answer 3

User77042963 posted

If you are using SQL Server 2017 or 2019, you can use string_agg function to build the dynamic column list.

CREATE TABLE [dbo].[ShoppingDetail](
	[CustomerName] [varchar](256) NOT NULL,
	[ProductCategory] [varchar](256) NOT NULL,
	[TotalAmount] [numeric](18, 2) NOT NULL,
	[Date] [date] NULL
)

INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Roy Martin', N'Clothing', CAST(2967.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
,(N'Roy Martin', N'Accessories', CAST(2458.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
, (N'Roy Martin', N'Bikes', CAST(52478.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
, (N'Caitlin C Watson', N'Clothing', CAST(3289.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
, (N'Caitlin C Watson', N'Bikes', CAST(75769.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
,(N'Taylor Torres', N'Clothing', CAST(2875.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
,(N'Taylor Torres', N'Accessories', CAST(6567.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
,(N'Taylor Torres', N'Bikes', CAST(46897.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
, (N'Taylor Torres', N'Food Products', CAST(15783.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))

declare @ColumnHeaders NVARCHAR(4000) ;
declare @ColumnHeaders2 NVARCHAR(4000) ;
declare  @sql NVARCHAR(4000);
declare @Param int=7  --day
-- --===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
WHere n<= @Param ) ---Control the total columns list
,dates as (
Select n, DATEADD(day, -n+1, (Select max([date]) from ShoppingDetail) ) dt  
from(Select n from nums) D(n)
)
 
Select @ColumnHeaders = string_agg('SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end) as ' + Quotename(Convert(char(10),dt,120),'[')  , ','+char(10)+char(13))  WITHIN GROUP ( ORDER BY dt)
,@ColumnHeaders2 = string_agg('isnull(SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end),0) ' , '+'+char(10)+char(13))  WITHIN GROUP ( ORDER BY dt)
From dates  

SET @sql = N'Select  Case when [CustomerName] is null then ''Total'' else [CustomerName] end [CustomerName], '
+ @ColumnHeaders + ','+@ColumnHeaders2+' as [total] '
+' from [ShoppingDetail]
Group by grouping sets(CustomerName,())  '
  
 EXEC sp_executesql @sql;
     

drop table [ShoppingDetail]