Answered by:
Dynamic SQL Query for Date Column

Question
-
User2101416554 posted
This is the source https://www.sqlrelease.com/dynamic-pivot-query-in-sql-server
From the above instead of customer column i would to pivot using date column. Let me know how to do so?
Table
CREATE TABLE [dbo].[ShoppingDetail]( [CustomerName] [varchar](256) NOT NULL, [ProductCategory] [varchar](256) NOT NULL, [TotalAmount] [numeric](18, 2) NOT NULL, [Date] [date] NULL ) ON [PRIMARY] GO
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)) INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Roy Martin', N'Accessories', CAST(2458.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date)) INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Roy Martin', N'Bikes', CAST(52478.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date)) INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Caitlin C Watson', N'Clothing', CAST(3289.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date)) INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Caitlin C Watson', N'Bikes', CAST(75769.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date)) INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Taylor Torres', N'Clothing', CAST(2875.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date)) INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Taylor Torres', N'Accessories', CAST(6567.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date)) INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Taylor Torres', N'Bikes', CAST(46897.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date)) INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'Taylor Torres', N'Food Products', CAST(15783.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
Instead of customer in need date column pivot
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(CustomerName, '') + ']' FROM (SELECT DISTINCT CustomerName 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(CustomerName, '') + '], 0) AS [' + CustomerName + ']' FROM (SELECT DISTINCT CustomerName FROM dbo.ShoppingDetail)DT --Generate dynamic PIVOT query here SET @SQLStatement = N'SELECT ProductCategory ' + @PivotColumnsToSelect + ' FROM dbo.ShoppingDetail PIVOT ( SUM(TotalAmount) FOR CustomerName IN (' + @UniqueCustomersToPivot + ') ) AS PVT ' --Execute the dynamic t-sql PIVOT query below EXEC (@SQLStatement)
Pivot query
Thursday, December 26, 2019 7:53 AM
Answers
-
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)) --select * --from [ShoppingDetail] declare @ColumnHeaders 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, ''); --print @ColumnHeaders SET @sql = N'Select [CustomerName], ' + @ColumnHeaders +' from [ShoppingDetail] Group by CustomerName ' --print @sql EXEC sp_executesql @sql; drop table [ShoppingDetail]
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, December 26, 2019 4:25 PM -
User-719153870 posted
Hi umerfaiz123,
umerfaiz123@outlook.com
instead of customer column i would to pivot using date columnumerfaiz123@outlook.com
Instead of customer in need date column pivot
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(CustomerName, '') + ']' FROM (SELECT DISTINCT CustomerName 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(CustomerName, '') + '], 0) AS [' + CustomerName + ']' FROM (SELECT DISTINCT CustomerName FROM dbo.ShoppingDetail)DT --Generate dynamic PIVOT query here SET @SQLStatement = N'SELECT ProductCategory ' + @PivotColumnsToSelect + ' FROM dbo.ShoppingDetail PIVOT ( SUM(TotalAmount) FOR CustomerName IN (' + @UniqueCustomersToPivot + ') ) AS PVT ' --Execute the dynamic t-sql PIVOT query below EXEC (@SQLStatement)
Pivot query
The query result of above query you provided is:
According to the description, you want [date] as your query result's fields instead of [CustomerName] if i didn't misunderstand anything which should looks like below?
If so, you just need to change some little parts of your current query code:
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(convert(varchar(50),[Date]), '') + ']' 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(convert(varchar(50),[Date]), '') + '], 0) AS [' + convert(varchar(50),[Date]) + ']' FROM (SELECT DISTINCT [Date] FROM dbo.ShoppingDetail)DT --Generate dynamic PIVOT query here SET @SQLStatement = N'SELECT ProductCategory ' + @PivotColumnsToSelect + ' FROM dbo.ShoppingDetail PIVOT ( SUM(TotalAmount) FOR [Date] IN (' + @UniqueCustomersToPivot + ') ) AS PVT ' --Execute the dynamic t-sql PIVOT query below EXEC (@SQLStatement)
Or if i misunderstood anything please feel free to tell.
Best Regard,
Yang Shen
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Friday, December 27, 2019 3:12 AM
All replies
-
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)) --select * --from [ShoppingDetail] declare @ColumnHeaders 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, ''); --print @ColumnHeaders SET @sql = N'Select [CustomerName], ' + @ColumnHeaders +' from [ShoppingDetail] Group by CustomerName ' --print @sql EXEC sp_executesql @sql; drop table [ShoppingDetail]
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, December 26, 2019 4:25 PM -
User2101416554 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)) --select * --from [ShoppingDetail] declare @ColumnHeaders 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, ''); --print @ColumnHeaders SET @sql = N'Select [CustomerName], ' + @ColumnHeaders +' from [ShoppingDetail] Group by CustomerName ' --print @sql EXEC sp_executesql @sql; drop table [ShoppingDetail]
Thanks i cannot able to add extra column like ProductCategory
Friday, December 27, 2019 3:06 AM -
User-719153870 posted
Hi umerfaiz123,
umerfaiz123@outlook.com
instead of customer column i would to pivot using date columnumerfaiz123@outlook.com
Instead of customer in need date column pivot
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(CustomerName, '') + ']' FROM (SELECT DISTINCT CustomerName 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(CustomerName, '') + '], 0) AS [' + CustomerName + ']' FROM (SELECT DISTINCT CustomerName FROM dbo.ShoppingDetail)DT --Generate dynamic PIVOT query here SET @SQLStatement = N'SELECT ProductCategory ' + @PivotColumnsToSelect + ' FROM dbo.ShoppingDetail PIVOT ( SUM(TotalAmount) FOR CustomerName IN (' + @UniqueCustomersToPivot + ') ) AS PVT ' --Execute the dynamic t-sql PIVOT query below EXEC (@SQLStatement)
Pivot query
The query result of above query you provided is:
According to the description, you want [date] as your query result's fields instead of [CustomerName] if i didn't misunderstand anything which should looks like below?
If so, you just need to change some little parts of your current query code:
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(convert(varchar(50),[Date]), '') + ']' 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(convert(varchar(50),[Date]), '') + '], 0) AS [' + convert(varchar(50),[Date]) + ']' FROM (SELECT DISTINCT [Date] FROM dbo.ShoppingDetail)DT --Generate dynamic PIVOT query here SET @SQLStatement = N'SELECT ProductCategory ' + @PivotColumnsToSelect + ' FROM dbo.ShoppingDetail PIVOT ( SUM(TotalAmount) FOR [Date] IN (' + @UniqueCustomersToPivot + ') ) AS PVT ' --Execute the dynamic t-sql PIVOT query below EXEC (@SQLStatement)
Or if i misunderstood anything please feel free to tell.
Best Regard,
Yang Shen
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Friday, December 27, 2019 3:12 AM -
User2101416554 posted
Hi umerfaiz123,
umerfaiz123@outlook.com
instead of customer column i would to pivot using date columnumerfaiz123@outlook.com
Instead of customer in need date column pivot
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(CustomerName, '') + ']' FROM (SELECT DISTINCT CustomerName 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(CustomerName, '') + '], 0) AS [' + CustomerName + ']' FROM (SELECT DISTINCT CustomerName FROM dbo.ShoppingDetail)DT --Generate dynamic PIVOT query here SET @SQLStatement = N'SELECT ProductCategory ' + @PivotColumnsToSelect + ' FROM dbo.ShoppingDetail PIVOT ( SUM(TotalAmount) FOR CustomerName IN (' + @UniqueCustomersToPivot + ') ) AS PVT ' --Execute the dynamic t-sql PIVOT query below EXEC (@SQLStatement)
Pivot query
The query result of above query you provided is:
According to the description, you want [date] as your query result's fields instead of [CustomerName] if i didn't misunderstand anything which should looks like below?
If so, you just need to change some little parts of your current query code:
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(convert(varchar(50),[Date]), '') + ']' 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(convert(varchar(50),[Date]), '') + '], 0) AS [' + convert(varchar(50),[Date]) + ']' FROM (SELECT DISTINCT [Date] FROM dbo.ShoppingDetail)DT --Generate dynamic PIVOT query here SET @SQLStatement = N'SELECT ProductCategory ' + @PivotColumnsToSelect + ' FROM dbo.ShoppingDetail PIVOT ( SUM(TotalAmount) FOR [Date] IN (' + @UniqueCustomersToPivot + ') ) AS PVT ' --Execute the dynamic t-sql PIVOT query below EXEC (@SQLStatement)
Or if i misunderstood anything please feel free to tell.
Best Regard,
Yang Shen
Final Query 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)
How to calculate row and column total from the above result set
Friday, December 27, 2019 3:36 AM -
User-719153870 posted
Hi umerfaiz123,
How to calculate row and column total from the above result setGlad to see the question for this thread has been solved, as for the new question, you can open a new thread.
One question for one thread would help other people who faces the same issue find the solution much more easily. Thanks.
Best Regard,
Yang Shen
Friday, December 27, 2019 5:20 AM