locked
Dynamic SQL Query for Date Column RRS feed

  • 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 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.

    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 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.

    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 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.

    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 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

    Friday, December 27, 2019 5:20 AM