locked
SQL ROW Grand Total Value field mismatching RRS feed

  • Question

  • User2101416554 posted

    From the following thread https://forums.asp.net/t/2162830.aspx?Date+Day+Week+No+together+in+SQL+Pivot

    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))
    
    This is the query
    
    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]
    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(@ColumnName,',',',')
    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 above query is working fine but the ROW Grand Total is not matching i think it showing the value in the date 3 column.

    Saturday, January 4, 2020 7:09 AM

Answers

  • User2101416554 posted

    I figure it out ...

    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)

    Final query ...

    I have change the @s in row total as well which is highlighted in BOLD. Now its working fine.

    DECLARE @RowForSum AS NVARCHAR(MAX)
    SELECT @RowForSum = REPLACE(@s,',',',')
    SELECT @RowForSum = REPLACE(@RowForSum,'[','SUM(ISNULL([')
    SELECT @RowForSum = REPLACE(@RowForSum,']','],0))')

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 6, 2020 5:32 AM

All replies

  • User2101416554 posted

    The query works fine if we use @ColumnName

    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]
    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(@ColumnName,',',',')
    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,'+ @ColumnName +',Summa as Total FROM cte
    UNION ALL
    SELECT '' '',''Total'', '+ @RowForSum +',SUM(Summa) FROM cte'
    exec (@DynamicPivotQuery)
    CustomerName	ProductCategory	02-Jan-2020	10-Jan-2020	23-Dec-2019	27-Dec-2019	28-Dec-2019	30-Dec-2019	Total
    A	A1	7616.70	NULL	NULL	NULL	NULL	NULL	7616.70
    A	A2	6377.94	NULL	NULL	NULL	NULL	NULL	6377.94
    A	A3	6803.14	NULL	NULL	NULL	NULL	NULL	6803.14
    A	A4	NULL	NULL	NULL	NULL	27408.96	NULL	27408.96
    A	A5	NULL	NULL	NULL	31796.70	NULL	NULL	31796.70
    A	A6	NULL	NULL	NULL	6117.54	NULL	NULL	6117.54
    B	B1	15840.00	NULL	NULL	NULL	NULL	NULL	15840.00
    B	B10	1247.40	NULL	NULL	NULL	NULL	NULL	1247.40
    B	B11	NULL	NULL	NULL	NULL	NULL	1484.01	1484.01
    B	B12	2518.56	NULL	NULL	NULL	NULL	NULL	2518.56
    B	B13	2046.33	NULL	NULL	NULL	NULL	NULL	2046.33
    B	B14	7378.27	NULL	NULL	NULL	NULL	NULL	7378.27
    B	B15	2518.56	NULL	NULL	NULL	NULL	NULL	2518.56
    B	B16	2203.74	NULL	NULL	NULL	NULL	NULL	2203.74
    B	B17	9129.78	NULL	NULL	NULL	NULL	NULL	9129.78
    B	B18	38030.26	NULL	NULL	NULL	NULL	NULL	38030.26
    B	B19	7378.27	NULL	NULL	NULL	NULL	NULL	7378.27
    B	B2	5346.00	NULL	NULL	NULL	NULL	NULL	5346.00
    B	B20	8137.80	NULL	NULL	NULL	NULL	NULL	8137.80
    B	B21	NULL	NULL	NULL	NULL	NULL	1574.10	1574.10
    B	B22	3148.20	NULL	NULL	NULL	NULL	NULL	3148.20
    B	B23	10644.98	NULL	NULL	NULL	NULL	NULL	10644.98
    B	B24	29909.88	NULL	NULL	NULL	NULL	NULL	29909.88
    B	B25	21624.57	NULL	NULL	NULL	NULL	NULL	21624.57
    B	B26	1851.30	NULL	NULL	NULL	NULL	NULL	1851.30
    B	B27	2776.95	NULL	NULL	NULL	NULL	NULL	2776.95
    B	B28	5936.04	NULL	NULL	NULL	NULL	NULL	5936.04
    B	B29	2374.42	NULL	NULL	NULL	NULL	NULL	2374.42
    B	B3	8731.80	NULL	NULL	NULL	NULL	NULL	8731.80
    B	B30	1780.81	NULL	NULL	NULL	NULL	NULL	1780.81
    B	B31	1929.21	NULL	NULL	NULL	NULL	NULL	1929.21
    B	B32	2077.61	NULL	NULL	NULL	NULL	NULL	2077.61
    B	B33	3580.63	NULL	NULL	NULL	NULL	NULL	3580.63
    B	B4	7900.20	NULL	NULL	NULL	NULL	NULL	7900.20
    B	B5	6652.80	NULL	NULL	NULL	NULL	NULL	6652.80
    B	B6	7484.40	NULL	NULL	NULL	NULL	NULL	7484.40
    B	B7	3960.00	NULL	NULL	NULL	NULL	NULL	3960.00
    B	B8	3960.00	NULL	NULL	NULL	NULL	NULL	3960.00
    B	B9	8137.80	NULL	NULL	NULL	NULL	NULL	8137.80
    C	C1	NULL	NULL	NULL	1356.39	NULL	NULL	1356.39
    C	C2	NULL	NULL	NULL	NULL	NULL	38711.97	38711.97
    C	C3	NULL	NULL	23.60	NULL	NULL	NULL	23.60
    C	C4	NULL	NULL	NULL	NULL	NULL	3941.38	3941.38
    C	C5	3950.59	NULL	NULL	NULL	NULL	NULL	3950.59
    C	C6	NULL	25901.87	NULL	NULL	NULL	NULL	25901.87
    C	C7	NULL	4998.72	NULL	NULL	NULL	NULL	4998.72
    D	D1	NULL	74477.79	NULL	NULL	NULL	NULL	74477.79
    D	D2	NULL	145753.47	NULL	NULL	NULL	NULL	145753.47
     	Total	260984.94	251131.85	23.60	39270.63	27408.96	45711.46	624531.44

    But when we use @s then the ROW GRAND TOTAL SHOWS DIFFERENT VALUE which is LAST ROW

    We use @s to get Date,Day and WeekNo ... Whats going on in the query?

    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]
    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(@ColumnName,',',',')
    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)
    CustomerName	ProductCategory	23-Dec-2019 (Mon | Wk 52)	27-Dec-2019 (Fri | Wk 52)	28-Dec-2019 (Sat | Wk 52)	30-Dec-2019 (Mon | Wk 1)	02-Jan-2020 (Thu | Wk 1)	10-Jan-2020 (Fri | Wk 2)	Total
    A	A1	NULL	NULL	NULL	NULL	7616.70	NULL	7616.70
    A	A2	NULL	NULL	NULL	NULL	6377.94	NULL	6377.94
    A	A3	NULL	NULL	NULL	NULL	6803.14	NULL	6803.14
    A	A4	NULL	NULL	27408.96	NULL	NULL	NULL	27408.96
    A	A5	NULL	31796.70	NULL	NULL	NULL	NULL	31796.70
    A	A6	NULL	6117.54	NULL	NULL	NULL	NULL	6117.54
    B	B1	NULL	NULL	NULL	NULL	15840.00	NULL	15840.00
    B	B10	NULL	NULL	NULL	NULL	1247.40	NULL	1247.40
    B	B11	NULL	NULL	NULL	1484.01	NULL	NULL	1484.01
    B	B12	NULL	NULL	NULL	NULL	2518.56	NULL	2518.56
    B	B13	NULL	NULL	NULL	NULL	2046.33	NULL	2046.33
    B	B14	NULL	NULL	NULL	NULL	7378.27	NULL	7378.27
    B	B15	NULL	NULL	NULL	NULL	2518.56	NULL	2518.56
    B	B16	NULL	NULL	NULL	NULL	2203.74	NULL	2203.74
    B	B17	NULL	NULL	NULL	NULL	9129.78	NULL	9129.78
    B	B18	NULL	NULL	NULL	NULL	38030.26	NULL	38030.26
    B	B19	NULL	NULL	NULL	NULL	7378.27	NULL	7378.27
    B	B2	NULL	NULL	NULL	NULL	5346.00	NULL	5346.00
    B	B20	NULL	NULL	NULL	NULL	8137.80	NULL	8137.80
    B	B21	NULL	NULL	NULL	1574.10	NULL	NULL	1574.10
    B	B22	NULL	NULL	NULL	NULL	3148.20	NULL	3148.20
    B	B23	NULL	NULL	NULL	NULL	10644.98	NULL	10644.98
    B	B24	NULL	NULL	NULL	NULL	29909.88	NULL	29909.88
    B	B25	NULL	NULL	NULL	NULL	21624.57	NULL	21624.57
    B	B26	NULL	NULL	NULL	NULL	1851.30	NULL	1851.30
    B	B27	NULL	NULL	NULL	NULL	2776.95	NULL	2776.95
    B	B28	NULL	NULL	NULL	NULL	5936.04	NULL	5936.04
    B	B29	NULL	NULL	NULL	NULL	2374.42	NULL	2374.42
    B	B3	NULL	NULL	NULL	NULL	8731.80	NULL	8731.80
    B	B30	NULL	NULL	NULL	NULL	1780.81	NULL	1780.81
    B	B31	NULL	NULL	NULL	NULL	1929.21	NULL	1929.21
    B	B32	NULL	NULL	NULL	NULL	2077.61	NULL	2077.61
    B	B33	NULL	NULL	NULL	NULL	3580.63	NULL	3580.63
    B	B4	NULL	NULL	NULL	NULL	7900.20	NULL	7900.20
    B	B5	NULL	NULL	NULL	NULL	6652.80	NULL	6652.80
    B	B6	NULL	NULL	NULL	NULL	7484.40	NULL	7484.40
    B	B7	NULL	NULL	NULL	NULL	3960.00	NULL	3960.00
    B	B8	NULL	NULL	NULL	NULL	3960.00	NULL	3960.00
    B	B9	NULL	NULL	NULL	NULL	8137.80	NULL	8137.80
    C	C1	NULL	1356.39	NULL	NULL	NULL	NULL	1356.39
    C	C2	NULL	NULL	NULL	38711.97	NULL	NULL	38711.97
    C	C3	23.60	NULL	NULL	NULL	NULL	NULL	23.60
    C	C4	NULL	NULL	NULL	3941.38	NULL	NULL	3941.38
    C	C5	NULL	NULL	NULL	NULL	3950.59	NULL	3950.59
    C	C6	NULL	NULL	NULL	NULL	NULL	25901.87	25901.87
    C	C7	NULL	NULL	NULL	NULL	NULL	4998.72	4998.72
    D	D1	NULL	NULL	NULL	NULL	NULL	74477.79	74477.79
    D	D2	NULL	NULL	NULL	NULL	NULL	145753.47	145753.47
     	Total	260984.94	251131.85	23.60	39270.63	27408.96	45711.46	624531.44
    Monday, January 6, 2020 4:38 AM
  • User2101416554 posted

    I figure it out ...

    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)

    Final query ...

    I have change the @s in row total as well which is highlighted in BOLD. Now its working fine.

    DECLARE @RowForSum AS NVARCHAR(MAX)
    SELECT @RowForSum = REPLACE(@s,',',',')
    SELECT @RowForSum = REPLACE(@RowForSum,'[','SUM(ISNULL([')
    SELECT @RowForSum = REPLACE(@RowForSum,']','],0))')

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 6, 2020 5:32 AM