Dynamic Pivot With Row and Column Total Weekly

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.

Answer 1

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

Answer 2

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

Answer 3

User77042963 posted

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

Answer 4

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

Answer 5

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?