SQL Server Add row/column/grant totals in dynamic pivot table

epfja78i 于 2023-03-17 发布在其他

关注(0)|答案(3)|浏览(142)

I am trying to add row/column/grant totals (how many customers each of seller serviced and how many sellers each of customer had interacted with) for a dynamic pivot table in SQL query.

Since there are unknown numbers of sellers (columns in pivot table) in the actual data, I have to use dynamic pivot. I tried to use GROUP BY ROLLUP() and GROUP BY CUBE() but had failed to find the right place to fit them in. Any suggestion will be greatly appreciated!

Here is the sample data:

create table t
(
  Id INT NOT NULL,
  Customer  varchar(5),
  StartDay date,
  Seller varchar(10)
);

insert into t values 
(1,       'Bob',      '1/4/2019',            'Joe'),
(1,       'Bob',      '2/7/2021',              'Joe'),
(2,       'Kime',      '8/9/2018',             'Tedy'),
(2,       'Kime',      '1/11/2019',             'Tedy'),
(2,       'Kime',      '10/1/2020',             'Anny'),
(3,       'Kelly',      '10/1/2020',             'Angle'),
(4,       'Ben',      '7/8/2020',           'Anny');

Here is the query with dynamic pivot table I have:

DECLARE @cols   AS NVARCHAR(MAX),  
        @query  AS NVARCHAR(MAX)
 
select @cols = STUFF((SELECT ',' + QUOTENAME(Seller) 
                    from t
                    group by Seller
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

 set @query = N'SELECT Customer,' + @cols + N' from        
  (select Customer, Seller
  from t
            ) x
            pivot 
            ( count(Seller)
                for Seller in (' + @cols + N')
            ) p '

exec sp_executesql @query;

Below is my expected outcome:

Customer    Angle   Anny    Joe     Tedy   Total
Ben         0       1       0       0      1
Bob         0       0       2       0      2
Kelly       1       0       0       0      1 
Kime        0       1       0       2      3
Total       1       2       2       2      7 (Grand total)

sql-server

来源：https://stackoverflow.com/questions/75715419/add-row-column-grant-totals-in-dynamic-pivot-table

3条答案

按热度按时间

sgtfey8w1#

Here is a brute force approach ... UNION ALL

DECLARE @cols   AS NVARCHAR(MAX),  
        @query  AS NVARCHAR(MAX)
 
select @cols = STUFF((SELECT ',' + QUOTENAME(Seller) 
                    from t
                    group by Seller
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

 set @query = N'
 Select * 
  From (
         Select Customer, Seller from t
         Union All
         Select ''Total'',Seller From t
         Union All 
         Select Customer,''Total'' From t
         Union All
         Select ''Total'',''Total'' From t
        ) x
 pivot  ( count(Seller) for Seller in (' + @cols + N',[Total]) ) p '

exec sp_executesql @query;

Results

赞(0）回复(0）举报 2023-03-17

u1ehiz5o2#

You can use GROUPING SETS for this, which can be much easier than messing around with UNION ALL . You can also combine that with a manual pivot using COUNT(CASE

DECLARE
    @cols   AS NVARCHAR(MAX)  
,   @query  AS NVARCHAR(MAX);
 
SET @cols = (
    SELECT '
  COUNT(CASE WHEN Seller = ' + QUOTENAME(Seller, '''') + ' THEN 1 END) AS ' + QUOTENAME(Seller) + ','
    FROM t
    GROUP BY Seller
    FOR XML PATH(''), TYPE
 ).value('text()[1]', 'NVARCHAR(MAX)')

SET @query = N'
SELECT
  ISNULL(Customer, ''Totals'') AS Customer,' + @cols + '
  COUNT(*) AS Totals
FROM t
GROUP BY GROUPING SETS (
    (Customer),
    ()
)
ORDER BY
  GROUPING(Customer);
';

PRINT @query;   -- your friend

EXEC sp_executesql @query;

db<>fiddle

赞(0）回复(0）举报 2023-03-17

cwtwac6a3#

John kinda beat me to it, but here's a bit more convoluted version that uses a few less UNIONs:

drop table #t
create table #t
(
  Id INT NOT NULL,
  Customer  varchar(5),
  StartDay date,
  Seller varchar(10)
);

insert into #t values 
(1,       'Bob',      '1/4/2019',            'Joe'),
(1,       'Bob',      '2/7/2021',              'Joe'),
(2,       'Kime',      '8/9/2018',             'Tedy'),
(2,       'Kime',      '1/11/2019',             'Tedy'),
(2,       'Kime',      '10/1/2020',             'Anny'),
(3,       'Kelly',      '10/1/2020',             'Angle'),
(4,       'Ben',      '7/8/2020',           'Anny');

DECLARE @cols   AS NVARCHAR(MAX)  
,   @query  AS NVARCHAR(MAX)
,   @cols2  AS NVARCHAR(MAX)
 
select @cols = STUFF((SELECT ',' + QUOTENAME(Seller) 
                    from #t
                    group by Seller
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
,   @cols2 = STUFF((SELECT '+' + QUOTENAME(Seller) 
                    from #t
                    group by Seller
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

 set @query = N'
    SELECT Customer,' + @cols + ',' + @cols2 + ' AS totals
    from        
    (
        SELECT  Customer, Seller, 0 AS sort
        FROM    #t
        UNION ALL
        SELECT  ''Totals'', Seller, -1 AS sort
        FROM    #t
    ) x
    pivot 
    ( count(Seller)
    for Seller in (' + @cols + N')
    ) p 
   order by sort DESC
            '
exec sp_executesql @query;

The difference is that i manually create Totals column by summarizing the other Pivots, as well as a bit more explicit sorting.

I also have to add that while this is doable in SQL, usually it's better to perform this sort of pivot in your application / reporting tool

赞(0）回复(0）举报 2023-03-17

我来回答

SQL Server Add row/column/grant totals in dynamic pivot table

3条答案

相关问题

热门标签

最新问答