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)
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]
Dec 27, 2019 03:06 AM|umerfaiz123@outlook.com|LINK
limno
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
instead of customer column i would to pivot using date column
umerfaiz123@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.
Dec 27, 2019 03:36 AM|umerfaiz123@outlook.com|LINK
Yang Shen
Hi umerfaiz123,
umerfaiz123@outlook.com
instead of customer column i would to pivot using date column
umerfaiz123@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 withalias to be used inSELECt 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
--Generatedynamic 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
Member
12 Points
33 Posts
Dynamic SQL Query for Date Column
Dec 26, 2019 07:53 AM|umerfaiz123@outlook.com|LINK
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
Instead of customer in need date column pivot
Pivot query
All-Star
123252 Points
10024 Posts
Moderator
Re: Dynamic SQL Query for Date Column
Dec 26, 2019 04:25 PM|limno|LINK
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Member
12 Points
33 Posts
Re: Dynamic SQL Query for Date Column
Dec 27, 2019 03:06 AM|umerfaiz123@outlook.com|LINK
Thanks i cannot able to add extra column like ProductCategory
Contributor
3140 Points
983 Posts
Re: Dynamic SQL Query for Date Column
Dec 27, 2019 03:12 AM|Yang Shen|LINK
Hi umerfaiz123,
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:
Or if i misunderstood anything please feel free to tell.
Best Regard,
Yang Shen
Member
12 Points
33 Posts
Re: Dynamic SQL Query for Date Column
Dec 27, 2019 03:36 AM|umerfaiz123@outlook.com|LINK
How to calculate row and column total from the above result set
Contributor
3140 Points
983 Posts
Re: Dynamic SQL Query for Date Column
Dec 27, 2019 05:20 AM|Yang Shen|LINK
Hi umerfaiz123,
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