locked
Date, Day , Week No together in SQL Pivot RRS feed

  • Question

  • User2101416554 posted

    This is my table

    CREATE TABLE [dbo].[ShoppingDetail](
        [CustomerName] [varchar](256) NOT NULL,
        [ProductCategory] [varchar](256) NOT NULL,
        [TotalAmount] [numeric](18, 2) NOT NULL,
        [Total Value] [numeric](18, 2) NULL,
        [Date] [date] NULL
    ) ON [PRIMARY]
    GO
     
    
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Roy Martin', N'Clothing', CAST(2967.00 AS Numeric(18, 2)), CAST(1500.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Roy Martin', N'Accessories', CAST(2458.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Roy Martin', N'Bikes', CAST(0.00 AS Numeric(18, 2)), CAST(2500.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Caitlin C Watson', N'Clothing', CAST(3289.00 AS Numeric(18, 2)), CAST(4000.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Caitlin C Watson', N'Bikes', CAST(75769.00 AS Numeric(18, 2)), CAST(5000.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Clothing', CAST(0.00 AS Numeric(18, 2)), CAST(7000.00 AS Numeric(18, 2)), CAST(N'2019-12-14' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Accessories', CAST(6567.00 AS Numeric(18, 2)), CAST(6500.00 AS Numeric(18, 2)), CAST(N'2019-12-10' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Bikes', CAST(46897.00 AS Numeric(18, 2)), CAST(3200.00 AS Numeric(18, 2)), CAST(N'2019-12-11' AS Date))
    INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Total Value], [Date]) VALUES (N'Taylor Torres', N'Clothing', CAST(15783.00 AS Numeric(18, 2)), NULL, CAST(N'2019-12-14' AS Date))
    
    declare @ColumnHeaders NVARCHAR(4000) ;
    declare @ColumnHeaders2 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(REPLACE(CONVERT(CHAR(11), Date, 106),' ','-')) from ShoppingDetail where date is not null) ) 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)+' ( '+datename(weekday,dt)+' | Week'+datename(ISO_WEEK,dt)+' )','[') + char(10)+char(13)
    FROM  dates  Order by n desc FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')  
    ,
    @ColumnHeaders2 = STUFF( (SELECT '+' + 'isnull(SUM (Case when [Date]='+ quotename( Convert(char(8),dt,112),'''') +' then  TotalAmount  else null end),0) '   + char(10)+char(13)
    FROM  dates Order by n desc FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')  
      
     From dates t1
     
     
       
    SET @sql = N'Select  Case when [CustomerName] is null then ''Total'' else [CustomerName] end [CustomerName], '
    + @ColumnHeaders + ','+@ColumnHeaders2+' as [Total] '
    +' from [ShoppingDetail]
    Group by grouping sets(CustomerName,())  '
       
    --print @sql
     EXEC sp_executesql @sql;

    This query is working fine. Like the above query i need date,day,week no all together as column in the below query ...

    With the below query i need Date,Day, Week No Eg: 08-Dec-2019(Sunday,Week49)
    
    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)

    With the above query i need Date,Day, Week No Eg: 08-Dec-2019(Sunday,Week49)

    Tuesday, December 31, 2019 10:15 AM

Answers

  • User-719153870 posted

    Hi umerfaiz123,

    To solve the problem, you need to update the original sql query which you put in @sql. Use PRINT to check it, which you can see below:

    ;WITH cte
    AS (
        SELECT * FROM
        (
            SELECT CustomerName,ProductCategory,[10-Dec-2019],[11-Dec-2019],[14-Dec-2019]
            ,SUM(ISNULL([10-Dec-2019],0)+ISNULL([11-Dec-2019],0)+ISNULL([14-Dec-2019],0)) Summa
            FROM (
    SELECT Distinct CustomerName,ProductCategory,TotalAmount,Date from [ShoppingDetail]
                )t
            pivot
            (
                MAX(TotalAmount) FOR Date IN ([10-Dec-2019],[11-Dec-2019],[14-Dec-2019])
            ) piv GROUP BY CustomerName,ProductCategory,[10-Dec-2019],[11-Dec-2019],[14-Dec-2019]
        ) r
    )
    SELECT CustomerName,ProductCategory,[10-Dec-2019],[11-Dec-2019],[14-Dec-2019],Summa as Total FROM cte
    UNION ALL
    SELECT '','Total',SUM(ISNULL([10-Dec-2019],0)),SUM(ISNULL([11-Dec-2019],0)),SUM(ISNULL([14-Dec-2019],0)),SUM(Summa) FROM cte

    As you can see, all you need to do is to add 'as ....' in the highlighted place.

    However, the parameter @ColumnName is complex to modify directly. Thus, i suggest that you can create a new parameter to do the add 'as ...' job.

    Below i made a demo that you can refer to:

    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+'('+datename(weekday,@dd)+',Week'+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 highlighted places are those need to be added or updated.

    Below is the query result of this demo:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 1, 2020 5:57 AM

All replies

  • User-719153870 posted

    Hi umerfaiz123,

    To solve the problem, you need to update the original sql query which you put in @sql. Use PRINT to check it, which you can see below:

    ;WITH cte
    AS (
        SELECT * FROM
        (
            SELECT CustomerName,ProductCategory,[10-Dec-2019],[11-Dec-2019],[14-Dec-2019]
            ,SUM(ISNULL([10-Dec-2019],0)+ISNULL([11-Dec-2019],0)+ISNULL([14-Dec-2019],0)) Summa
            FROM (
    SELECT Distinct CustomerName,ProductCategory,TotalAmount,Date from [ShoppingDetail]
                )t
            pivot
            (
                MAX(TotalAmount) FOR Date IN ([10-Dec-2019],[11-Dec-2019],[14-Dec-2019])
            ) piv GROUP BY CustomerName,ProductCategory,[10-Dec-2019],[11-Dec-2019],[14-Dec-2019]
        ) r
    )
    SELECT CustomerName,ProductCategory,[10-Dec-2019],[11-Dec-2019],[14-Dec-2019],Summa as Total FROM cte
    UNION ALL
    SELECT '','Total',SUM(ISNULL([10-Dec-2019],0)),SUM(ISNULL([11-Dec-2019],0)),SUM(ISNULL([14-Dec-2019],0)),SUM(Summa) FROM cte

    As you can see, all you need to do is to add 'as ....' in the highlighted place.

    However, the parameter @ColumnName is complex to modify directly. Thus, i suggest that you can create a new parameter to do the add 'as ...' job.

    Below i made a demo that you can refer to:

    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+'('+datename(weekday,@dd)+',Week'+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 highlighted places are those need to be added or updated.

    Below is the query result of this demo:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 1, 2020 5:57 AM
  • User2101416554 posted

    Hi umerfaiz123,

    To solve the problem, you need to update the original sql query which you put in @sql. Use PRINT to check it, which you can see below:

    ;WITH cte
    AS (
        SELECT * FROM
        (
            SELECT CustomerName,ProductCategory,[10-Dec-2019],[11-Dec-2019],[14-Dec-2019]
            ,SUM(ISNULL([10-Dec-2019],0)+ISNULL([11-Dec-2019],0)+ISNULL([14-Dec-2019],0)) Summa
            FROM (
    SELECT Distinct CustomerName,ProductCategory,TotalAmount,Date from [ShoppingDetail]
                )t
            pivot
            (
                MAX(TotalAmount) FOR Date IN ([10-Dec-2019],[11-Dec-2019],[14-Dec-2019])
            ) piv GROUP BY CustomerName,ProductCategory,[10-Dec-2019],[11-Dec-2019],[14-Dec-2019]
        ) r
    )
    SELECT CustomerName,ProductCategory,[10-Dec-2019],[11-Dec-2019],[14-Dec-2019],Summa as Total FROM cte
    UNION ALL
    SELECT '','Total',SUM(ISNULL([10-Dec-2019],0)),SUM(ISNULL([11-Dec-2019],0)),SUM(ISNULL([14-Dec-2019],0)),SUM(Summa) FROM cte

    As you can see, all you need to do is to add 'as ....' in the highlighted place.

    However, the parameter @ColumnName is complex to modify directly. Thus, i suggest that you can create a new parameter to do the add 'as ...' job.

    Below i made a demo that you can refer to:

    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+'('+datename(weekday,@dd)+',Week'+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 highlighted places are those need to be added or updated.

    Below is the query result of this demo:

    Best Regard,

    Yang Shen

    One word just accurate ...!

    Wednesday, January 1, 2020 10:06 AM