locked
Pass Date Parameter in CTE in SQL RRS feed

  • Question

  • User2101416554 posted

    How to pass date parameter for the below query ...

    Like where Date  between '01-12-2019' and '31-12-2019' and also Date column should not be NULL

    declare @i int=1
    declare @d NVARCHAR(50)
    declare @dd date
    declare @s NVARCHAR(MAX)=' '
    while @i<=(select count(distinct(Date)) from ShoppingDetail )
    begin
    set @dd=(select top 1 s.Date from (select distinct(Date) from ShoppingDetail)s where s.Date not in (select top (@i-1) s.Date from (select distinct(Date) from ShoppingDetail )s))
    set @d= (REPLACE(CONVERT(CHAR(11),@dd, 106),' ','-'))
    set @s+=QUOTENAME(REPLACE(CONVERT(CHAR(11),@dd, 106),' ','-'))+'as '''+@d+' ('+ LEFT( datename(weekday,@dd) ,3) +' | Wk '+datename(ISO_WEEK,@dd)+')'' ,'
    set @i+=1
    end
    set @s=SUBSTRING(@s,1,LEN(@s)-1)
    
    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
    SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') ) FROM (SELECT DISTINCT REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') as Date FROM ShoppingDetail ) AS [Date] Order By Date
    DECLARE @ColumnForSum AS NVARCHAR(MAX)
    SELECT @ColumnForSum = REPLACE(@ColumnName,',','+')
    SELECT @ColumnForSum = REPLACE(@ColumnForSum,'[','ISNULL([')
    SELECT @ColumnForSum = REPLACE(@ColumnForSum,']','],0)')
       
    DECLARE @RowForSum AS NVARCHAR(MAX)
    SELECT @RowForSum = REPLACE(@s,',',',')
    SELECT @RowForSum = REPLACE(@RowForSum,'[','SUM(ISNULL([')
    SELECT @RowForSum = REPLACE(@RowForSum,']','],0))')
    
       
    SET @DynamicPivotQuery =
    ';WITH cte
    AS (
        SELECT * FROM
        (
            SELECT CustomerName,ProductCategory,'+@ColumnName+' ,SUM('+@ColumnForSum+') Summa
            FROM  ( SELECT Distinct CustomerName,ProductCategory,TotalAmount,Date from [ShoppingDetail] ) t
            pivot ( MAX(TotalAmount) FOR Date IN ('+ @ColumnName +') ) piv GROUP BY CustomerName,ProductCategory,'+ @ColumnName +'
        ) r
    )
    SELECT CustomerName,ProductCategory,'+ @s +',Summa as Total FROM cte 
    UNION ALL
    SELECT '' '',''Total'', '+ @RowForSum +',SUM(Summa) FROM cte  '
    exec (@DynamicPivotQuery)
    SELECT * FROM
        (
            SELECT CustomerName,ProductCategory,'+@ColumnName+' ,SUM('+@ColumnForSum+') Summa
            FROM  ( SELECT Distinct CustomerName,ProductCategory,TotalAmount,Date from [ShoppingDetail] Date is NOT NULL and Date between '01-12-2019' and  '31-12-2019' ) t
            pivot ( MAX(TotalAmount) FOR Date IN ('+ @ColumnName +') ) piv GROUP BY CustomerName,ProductCategory,'+ @ColumnName +'
        ) 

    I have added this but its not working .... Date is NOT NULL and Date between '01-12-2019' and '31-12-2019'

    Monday, January 6, 2020 10:30 AM

Answers

  • User288213138 posted

    Hi umerfaiz123@outlook.com,

    I have added this but its not working .... Date is NOT NULL and Date between '01-12-2019' and '31-12-2019'

    According to your description, you can refer to below code:

    declare @i int=1
    declare @d NVARCHAR(50)
    declare @dd date
    declare @s NVARCHAR(MAX)=' '
    while @i<=(select count(distinct(Date)) from ShoppingDetail where Date between '2019-12-1' and '2019-12-31' )
    begin
    set @dd=(select top 1 s.Date from (select distinct(Date) from ShoppingDetail where Date between '2019-12-1' and '2019-12-31')s where s.Date not in (select top (@i-1) s.Date from (select distinct(Date) from ShoppingDetail )s))
    set @d= (REPLACE(CONVERT(CHAR(11),@dd, 106),' ','-'))
    set @s+=QUOTENAME(REPLACE(CONVERT(CHAR(11),@dd, 106),' ','-'))+'as '''+@d+' ('+ LEFT( datename(weekday,@dd) ,3) +' | Wk '+datename(ISO_WEEK,@dd)+')'' ,'
    set @i+=1
    end
    set @s=SUBSTRING(@s,1,LEN(@s)-1)
    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
    SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') ) FROM (SELECT DISTINCT REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') as Date FROM ShoppingDetail ) AS [Date] Order By Date
    DECLARE @ColumnForSum AS NVARCHAR(MAX)
    SELECT @ColumnForSum = REPLACE(@ColumnName,',','+')
    SELECT @ColumnForSum = REPLACE(@ColumnForSum,'[','ISNULL([')
    SELECT @ColumnForSum = REPLACE(@ColumnForSum,']','],0)')
       
    DECLARE @RowForSum AS NVARCHAR(MAX)
    SELECT @RowForSum = REPLACE(@s,',',',')
    SELECT @RowForSum = REPLACE(@RowForSum,'[','SUM(ISNULL([')
    SELECT @RowForSum = REPLACE(@RowForSum,']','],0))')
    
       
    SET @DynamicPivotQuery =
    ';WITH cte
    AS (
        SELECT * FROM
        (
            SELECT CustomerName,ProductCategory,'+@ColumnName+' ,SUM('+@ColumnForSum+') Summa
            FROM  ( SELECT Distinct CustomerName,ProductCategory,TotalAmount,Date from [ShoppingDetail] ) t
            pivot ( MAX(TotalAmount) FOR Date IN ('+ @ColumnName +') ) piv GROUP BY CustomerName,ProductCategory,'+ @ColumnName +'
        ) r
    )
    SELECT CustomerName,ProductCategory,'+ @s +',Summa as Total FROM cte 
    UNION ALL
    SELECT '' '',''Total'', '+ @RowForSum +',SUM(Summa) FROM cte  '
    exec (@DynamicPivotQuery)
    

    The result:

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 7, 2020 5:39 AM

All replies

  • User452040443 posted

    Hi,

    I believe the Where clause was missing, and the part to check if the date is null is not necessary, because when the date is null it will not be within the period.

    Remembering that Date is a reserved word and must be enclosed in brackets or preceded by an alias.

    Hope this help

    Monday, January 6, 2020 12:21 PM
  • User2101416554 posted

    I have added the condition but still it is not working ...

    declare @i int=1
    declare @d NVARCHAR(50)
    declare @dd date
    declare @s NVARCHAR(MAX)=' '
    while @i<=(select count(distinct(Date)) from ShoppingDetail )
    begin
    set @dd=(select top 1 s.Date from (select distinct(Date) from ShoppingDetail)s where s.Date not in (select top (@i-1) s.Date from (select distinct(Date) from ShoppingDetail )s))
    set @d= (REPLACE(CONVERT(CHAR(11),@dd, 106),' ','-'))
    set @s+=QUOTENAME(REPLACE(CONVERT(CHAR(11),@dd, 106),' ','-'))+'as '''+@d+' ('+ LEFT( datename(weekday,@dd) ,3) +' | Wk '+datename(ISO_WEEK,@dd)+')'' ,'
    set @i+=1
    end
    set @s=SUBSTRING(@s,1,LEN(@s)-1)
    
    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
    SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') ) FROM (SELECT DISTINCT REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') as Date FROM ShoppingDetail ) AS [Date] Order By Date
    DECLARE @ColumnForSum AS NVARCHAR(MAX)
    SELECT @ColumnForSum = REPLACE(@ColumnName,',','+')
    SELECT @ColumnForSum = REPLACE(@ColumnForSum,'[','ISNULL([')
    SELECT @ColumnForSum = REPLACE(@ColumnForSum,']','],0)')
       
    DECLARE @RowForSum AS NVARCHAR(MAX)
    SELECT @RowForSum = REPLACE(@s,',',',')
    SELECT @RowForSum = REPLACE(@RowForSum,'[','SUM(ISNULL([')
    SELECT @RowForSum = REPLACE(@RowForSum,']','],0))')
    
       
    SET @DynamicPivotQuery =
    ';WITH cte
    AS (
        SELECT * FROM
        (
            SELECT CustomerName,ProductCategory,'+@ColumnName+' ,SUM('+@ColumnForSum+') Summa
            FROM  ( SELECT Distinct CustomerName,ProductCategory,TotalAmount,Date from [ShoppingDetail] Date is NOT NULL and Date between '01-12-2019' and  '31-12-2019' ) t
            pivot ( MAX(TotalAmount) FOR Date IN ('+ @ColumnName +') ) piv GROUP BY CustomerName,ProductCategory,'+ @ColumnName +'
        ) r
    )
    SELECT CustomerName,ProductCategory,'+ @s +',Summa as Total FROM cte 
    UNION ALL
    SELECT '' '',''Total'', '+ @RowForSum +',SUM(Summa) FROM cte  '
    exec (@DynamicPivotQuery)

    See in the highlighted color parameter query ...

    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'A', N'A1', CAST(7616.70 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'A', N'A2', CAST(6377.94 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'A', N'A3', CAST(6803.14 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'A', N'A4', CAST(27408.96 AS Numeric(18, 2)), CAST(N'2019-12-28' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'A', N'A5', CAST(31796.70 AS Numeric(18, 2)), CAST(N'2019-12-27' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'A', N'A6', CAST(6117.54 AS Numeric(18, 2)), CAST(N'2019-12-27' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B1', CAST(15840.00 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B2', CAST(5346.00 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B3', CAST(8731.80 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B4', CAST(7900.20 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B5', CAST(6652.80 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B6', CAST(7484.40 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B7', CAST(3960.00 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B8', CAST(3960.00 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B9', CAST(8137.80 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B10', CAST(1247.40 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B11', CAST(1484.01 AS Numeric(18, 2)), CAST(N'2019-12-30' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B12', CAST(2518.56 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B13', CAST(2046.33 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B14', CAST(7378.27 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B15', CAST(2518.56 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B16', CAST(2203.74 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B17', CAST(9129.78 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B18', CAST(38030.26 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B19', CAST(7378.27 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B20', CAST(8137.80 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B21', CAST(1574.10 AS Numeric(18, 2)), CAST(N'2019-12-30' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B22', CAST(3148.20 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B23', CAST(10644.98 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B24', CAST(29909.88 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B25', CAST(21624.57 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B26', CAST(1851.30 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B27', CAST(2776.95 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B28', CAST(5936.04 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B29', CAST(2374.42 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B30', CAST(1780.81 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B31', CAST(1929.21 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B32', CAST(2077.61 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B33', CAST(3580.63 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'C', N'C1', CAST(1356.39 AS Numeric(18, 2)), CAST(N'2019-12-27' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'C', N'C2', CAST(38711.97 AS Numeric(18, 2)), CAST(N'2019-12-30' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'C', N'C3', CAST(23.60 AS Numeric(18, 2)), CAST(N'2019-12-23' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'C', N'C4', CAST(3941.38 AS Numeric(18, 2)), CAST(N'2019-12-30' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'C', N'C5', CAST(3950.59 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'C', N'C6', CAST(25901.87 AS Numeric(18, 2)), CAST(N'2020-01-10' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'C', N'C7', CAST(4998.72 AS Numeric(18, 2)), CAST(N'2020-01-10' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'D', N'D1', CAST(74477.79 AS Numeric(18, 2)), CAST(N'2020-01-10' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'D', N'D2', CAST(145753.47 AS Numeric(18, 2)), CAST(N'2020-01-10' AS Date))

    See the table detail ...
    Tuesday, January 7, 2020 3:18 AM
  • User288213138 posted

    Hi umerfaiz123@outlook.com,

    I have added this but its not working .... Date is NOT NULL and Date between '01-12-2019' and '31-12-2019'

    According to your description, you can refer to below code:

    declare @i int=1
    declare @d NVARCHAR(50)
    declare @dd date
    declare @s NVARCHAR(MAX)=' '
    while @i<=(select count(distinct(Date)) from ShoppingDetail where Date between '2019-12-1' and '2019-12-31' )
    begin
    set @dd=(select top 1 s.Date from (select distinct(Date) from ShoppingDetail where Date between '2019-12-1' and '2019-12-31')s where s.Date not in (select top (@i-1) s.Date from (select distinct(Date) from ShoppingDetail )s))
    set @d= (REPLACE(CONVERT(CHAR(11),@dd, 106),' ','-'))
    set @s+=QUOTENAME(REPLACE(CONVERT(CHAR(11),@dd, 106),' ','-'))+'as '''+@d+' ('+ LEFT( datename(weekday,@dd) ,3) +' | Wk '+datename(ISO_WEEK,@dd)+')'' ,'
    set @i+=1
    end
    set @s=SUBSTRING(@s,1,LEN(@s)-1)
    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
    SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') ) FROM (SELECT DISTINCT REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') as Date FROM ShoppingDetail ) AS [Date] Order By Date
    DECLARE @ColumnForSum AS NVARCHAR(MAX)
    SELECT @ColumnForSum = REPLACE(@ColumnName,',','+')
    SELECT @ColumnForSum = REPLACE(@ColumnForSum,'[','ISNULL([')
    SELECT @ColumnForSum = REPLACE(@ColumnForSum,']','],0)')
       
    DECLARE @RowForSum AS NVARCHAR(MAX)
    SELECT @RowForSum = REPLACE(@s,',',',')
    SELECT @RowForSum = REPLACE(@RowForSum,'[','SUM(ISNULL([')
    SELECT @RowForSum = REPLACE(@RowForSum,']','],0))')
    
       
    SET @DynamicPivotQuery =
    ';WITH cte
    AS (
        SELECT * FROM
        (
            SELECT CustomerName,ProductCategory,'+@ColumnName+' ,SUM('+@ColumnForSum+') Summa
            FROM  ( SELECT Distinct CustomerName,ProductCategory,TotalAmount,Date from [ShoppingDetail] ) t
            pivot ( MAX(TotalAmount) FOR Date IN ('+ @ColumnName +') ) piv GROUP BY CustomerName,ProductCategory,'+ @ColumnName +'
        ) r
    )
    SELECT CustomerName,ProductCategory,'+ @s +',Summa as Total FROM cte 
    UNION ALL
    SELECT '' '',''Total'', '+ @RowForSum +',SUM(Summa) FROM cte  '
    exec (@DynamicPivotQuery)
    

    The result:

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 7, 2020 5:39 AM