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

Answer 1

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]

Answer 2

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

Answer 3

User-719153870 posted

Hi umerfaiz123,

[e-mail]

instead of customer column i would to pivot using date column

[e-mail]

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

Answer 4

User2101416554 posted

Hi umerfaiz123,

[e-mail]

instead of customer column i would to pivot using date column

[e-mail]

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

Answer 5

User-719153870 posted

Hi umerfaiz123,

How to calculate row and column total from the above result set

Glad 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