Dynamic Pivot query using DateTime Column in SQL Server

Last Reply 5 months ago By Andrea

Posted 5 months ago

 

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 i need date column pivot how to do so? 

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)

 

Posted 5 months ago
Hi @ashraft1,
Please try the following

Dynamic Pivot query using Year in DateTime Column in SQL Server

It might help you.

Cheers Andrea.