locked
Dynamic Pivot With Row and Column Total Weekly RRS feed

  • Question

  • User-1190924364 posted
    create table temp
    (
        date datetime,
        category varchar(3),
        amount money
    )
    
    insert into temp values ('1/1/2020', 'ABC', 1000.00)
    insert into temp values ('1/2/2020', 'DEF', 500.00)
    insert into temp values ('2/5/2020', 'GHI', 800.00)
    insert into temp values ('2/10/2020', 'DEF', 700.00)
    insert into temp values ('2/1/2020', 'ABC', 1100.00)
    
    
    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);
    
    SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(datename(ISO_WEEK,c.date)) 
                FROM temp c
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    set @query = 'SELECT Category, ' + @cols + ' from 
                (
                    select datename(ISO_WEEK,date) as date
                        , amount
                        , category
                    from temp
               ) x
                pivot 
                (
                     max(amount)
                    for date in (' + @cols + ')
                ) p '
    execute(@query)

    The above is Dynamic Pivot Query i need

    Week number , Row Total and Column total like below 

    Category	Wk-1	Wk-5	Wk-6	Wk-7	Total
    ABC	1000	1100	0	0	2100
    DEF	500	0	0	700	1200
    GHI	0	0	800	0	800
    G Total	1500	1100	800	700	4100
    

    Kindly let me know how to get the result like above output.

    Tuesday, June 2, 2020 11:31 AM

Answers

  • User77042963 posted
    create table temp
    (
        date datetime,
        category varchar(3),
        amount money
    )
    
    insert into temp values ('1/1/2020', 'ABC', 1000.00)
    insert into temp values ('1/2/2020', 'DEF', 500.00)
    insert into temp values ('2/5/2020', 'GHI', 800.00)
    insert into temp values ('2/10/2020', 'DEF', 700.00)
    insert into temp values ('2/1/2020', 'ABC', 1100.00)
    declare @ColumnHeaders NVARCHAR(max) ;
    declare @ColumnHeaders2 NVARCHAR(max) ;
    declare  @sql NVARCHAR(max);
     
    -- --===== 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) 
    ,dates as (
    Select n, DATEADD(day, -n+1, (Select max([date]) from temp) ) dt  
    from(Select n from nums  ) D(n)
    )
      
    Select @ColumnHeaders = STUFF( (SELECT distinct ',' + 'SUM (Case when datename(ISO_WEEK,date)='+ QUOTENAME(datename(ISO_WEEK,date),'''') +' then  amount  else null end) as ' + QUOTENAME('Wk-'+datename(ISO_WEEK,date),'[')  + char(10)+char(13)
    FROM  temp
     
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')   
    ,
    @ColumnHeaders2 = STUFF( (SELECT distinct '+' + 'isnull(SUM (Case when datename(ISO_WEEK,date)='+ QUOTENAME(datename(ISO_WEEK,date),'''') +' then  amount  else null end),0) '   + char(10)+char(13)
    FROM  temp 
     
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')   
      
     From dates t1
     
     
       
    SET @sql = N'Select   Case when [category] is null then ''Total'' else [category] end [category], '
    + @ColumnHeaders + ','+@ColumnHeaders2+' as [Total] '
    +' from [temp]
    Group by grouping sets(category,())  '
       
    --print @sql
     EXEC sp_executesql @sql;
      
    drop table temp

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 2, 2020 2:23 PM

All replies

  • User77042963 posted
    create table temp
    (
        date datetime,
        category varchar(3),
        amount money
    )
    
    insert into temp values ('1/1/2020', 'ABC', 1000.00)
    insert into temp values ('1/2/2020', 'DEF', 500.00)
    insert into temp values ('2/5/2020', 'GHI', 800.00)
    insert into temp values ('2/10/2020', 'DEF', 700.00)
    insert into temp values ('2/1/2020', 'ABC', 1100.00)
    declare @ColumnHeaders NVARCHAR(max) ;
    declare @ColumnHeaders2 NVARCHAR(max) ;
    declare  @sql NVARCHAR(max);
     
    -- --===== 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) 
    ,dates as (
    Select n, DATEADD(day, -n+1, (Select max([date]) from temp) ) dt  
    from(Select n from nums  ) D(n)
    )
      
    Select @ColumnHeaders = STUFF( (SELECT distinct ',' + 'SUM (Case when datename(ISO_WEEK,date)='+ QUOTENAME(datename(ISO_WEEK,date),'''') +' then  amount  else null end) as ' + QUOTENAME('Wk-'+datename(ISO_WEEK,date),'[')  + char(10)+char(13)
    FROM  temp
     
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')   
    ,
    @ColumnHeaders2 = STUFF( (SELECT distinct '+' + 'isnull(SUM (Case when datename(ISO_WEEK,date)='+ QUOTENAME(datename(ISO_WEEK,date),'''') +' then  amount  else null end),0) '   + char(10)+char(13)
    FROM  temp 
     
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')   
      
     From dates t1
     
     
       
    SET @sql = N'Select   Case when [category] is null then ''Total'' else [category] end [category], '
    + @ColumnHeaders + ','+@ColumnHeaders2+' as [Total] '
    +' from [temp]
    Group by grouping sets(category,())  '
       
    --print @sql
     EXEC sp_executesql @sql;
      
    drop table temp

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 2, 2020 2:23 PM
  • User-1190924364 posted

    Thanks what if i have other column as well to select ...

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[temp](
    	[date] [datetime] NULL,
    	[category] [varchar](3) NULL,
    	[amount] [money] NULL,
    	[Color] [varchar](50) NULL
    ) ON [PRIMARY]
    GO

    Color is the new column

    Insert query
    
    INSERT [dbo].[temp] ([date], [category], [amount], [Color]) VALUES (CAST(N'2020-01-01T00:00:00.000' AS DateTime), N'ABC', 1000.0000, N'Green')
    INSERT [dbo].[temp] ([date], [category], [amount], [Color]) VALUES (CAST(N'2020-01-02T00:00:00.000' AS DateTime), N'DEF', 500.0000, N'Blue')
    INSERT [dbo].[temp] ([date], [category], [amount], [Color]) VALUES (CAST(N'2020-02-05T00:00:00.000' AS DateTime), N'GHI', 800.0000, N'Blue')
    INSERT [dbo].[temp] ([date], [category], [amount], [Color]) VALUES (CAST(N'2020-02-10T00:00:00.000' AS DateTime), N'DEF', 700.0000, N'White')
    INSERT [dbo].[temp] ([date], [category], [amount], [Color]) VALUES (CAST(N'2020-01-02T00:00:00.000' AS DateTime), N'ABC', 1100.0000, N'Red')

    When i tried to select color column as well i got different result 

    declare @ColumnHeaders NVARCHAR(max) ;
    declare @ColumnHeaders2 NVARCHAR(max) ;
    declare  @sql NVARCHAR(max);
     
    -- --===== 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) 
    ,dates as (
    Select n, DATEADD(day, -n+1, (Select max([date]) from temp) ) dt  
    from(Select n from nums  ) D(n)
    )
      
    Select @ColumnHeaders = STUFF( (SELECT distinct ',' + 'SUM (Case when datename(ISO_WEEK,date)='+ QUOTENAME(datename(ISO_WEEK,date),'''') +' then  amount  else null end) as ' + QUOTENAME('Wk-'+datename(ISO_WEEK,date),'[')  + char(10)+char(13)
    FROM  temp
     
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')   
    ,
    @ColumnHeaders2 = STUFF( (SELECT distinct '+' + 'isnull(SUM (Case when datename(ISO_WEEK,date)='+ QUOTENAME(datename(ISO_WEEK,date),'''') +' then  amount  else null end),0) '   + char(10)+char(13)
    FROM  temp 
     
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')   
      
     From dates t1
     
     
       
    SET @sql = N'Select Color,  Case when [category] is null then ''Total'' else [category] end [category], '
    + @ColumnHeaders + ','+@ColumnHeaders2+' as [Total] '
    +' from [temp]
    Group by grouping sets(Color,category,())  '
       
    --print @sql
     EXEC sp_executesql @sql;

    Output

    Color	category	Wk-1	Wk-6	Wk-7	Total
    NULL	ABC	2100.00	NULL	NULL	2100.00
    NULL	DEF	500.00	NULL	700.00	1200.00
    NULL	GHI	NULL	800.00	NULL	800.00
    NULL	Total	2600.00	800.00	700.00	4100.00
    Blue	Total	500.00	800.00	NULL	1300.00
    Green	Total	1000.00	NULL	NULL	1000.00
    Red	Total	1100.00	NULL	NULL	1100.00
    White	Total	NULL	NULL	700.00	700.00

    Wednesday, June 3, 2020 4:25 AM
  • User77042963 posted

    What are you expecting with this new column in your result?

    Wednesday, June 3, 2020 2:02 PM
  • User-1190924364 posted

    I need the color column to be added in the below output

    Category        Wk-1    Wk-5    Wk-6    Wk-7    Total
    ABC     1000    1100    0       0       2100
    DEF     500     0       0       700     1200
    GHI     0       0       800     0       800
    G Total 1500    1100    800     700     4100
    

    When i tried to add color column i got the result like below which has null as color column

    Color   category        Wk-1    Wk-6    Wk-7    Total
    NULL    ABC     2100.00 NULL    NULL    2100.00
    NULL    DEF     500.00  NULL    700.00  1200.00
    NULL    GHI     NULL    800.00  NULL    800.00
    NULL    Total   2600.00 800.00  700.00  4100.00
    Blue    Total   500.00  800.00  NULL    1300.00
    Green   Total   1000.00 NULL    NULL    1000.00
    Red     Total   1100.00 NULL    NULL    1100.00
    White   Total   NULL    NULL    700.00  700.00
    

    Friday, June 12, 2020 6:53 AM
  • User77042963 posted

    "

    I need the color column to be added in the below output

    Category        Wk-1    Wk-5    Wk-6    Wk-7    Total
    ABC     1000    1100    0       0       2100
    DEF     500     0       0       700     1200
    GHI     0       0       800     0       800
    G Total 1500    1100    800     700     4100
    "

    Like what?
    How does your final result with the color column look?

    Check your source data to understand what you need.

    Where do you want to put your color column data in the dataset?

    Friday, June 12, 2020 7:22 PM