Dec 27, 2019 05:45 AM|umerfaiz123@outlook.com|LINK
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
I need Row and Column of this Pivot
How to add where condition like TotalAmount is not null
Re: Dynamic Pivot in SQL with ROW and COLUMN TOTAL
Dec 27, 2019 11:18 AM|umerfaiz123@outlook.com|LINK
umerfaiz123@outlook.com
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
TableData
INSERT [dbo].[ShoppingDetail]([CustomerName],[ProductCategory],[TotalAmount],[TotalValue],[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],[TotalValue],[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],[TotalValue],[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],[TotalValue],[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],[TotalValue],[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],[TotalValue],[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],[TotalValue],[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],[TotalValue],[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],[TotalValue],[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 withalias to be used inSELECt 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
--Generatedynamic 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
I need Row and Column of this Pivot
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/
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]
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]
Member
12 Points
33 Posts
Dynamic Pivot in SQL with ROW and COLUMN TOTAL
Dec 27, 2019 05:45 AM|umerfaiz123@outlook.com|LINK
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
Member
12 Points
33 Posts
Re: Dynamic Pivot in SQL with ROW and COLUMN TOTAL
Dec 27, 2019 11:18 AM|umerfaiz123@outlook.com|LINK
How to add more column is select query with the given below link. Like personname,pincode etc
All-Star
123088 Points
9964 Posts
Moderator
Re: Dynamic Pivot in SQL with ROW and COLUMN TOTAL
Dec 27, 2019 04:02 PM|limno|LINK
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]Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
All-Star
123088 Points
9964 Posts
Moderator
Re: Dynamic Pivot in SQL with ROW and COLUMN TOTAL
Dec 27, 2019 05:07 PM|limno|LINK
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]Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm