首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >复杂聚合视图如何

问复杂聚合视图如何
EN

Stack Overflow用户
提问于 2013-03-04 21:44:08
回答 2查看 126关注 0票数 0

复杂聚合视图如何

我有一个表( Table A ),里面有[payment type]、[Agent],[Amount Credit]和[Amount Debit]。现在,我正在寻找此数据的特定视图。

我想为每个座席提供他/她的活动摘要。

所以代理沿着x轴,支付类型沿着y轴,每个代理的总数也是如此。

代码语言:javascript
复制
Transaction type      Agent 1   Agent 2 

Amount Credit                 

Cash                20   40
Credit Card         20   20 

Total               40   60

Amount Debit                

Cash                20   40
Credit Card         10   10 

Total               30   50 

我试过了所有的方法,但还是不能得到这个视图。

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2013-03-04 22:41:21

您可以通过同时应用UNPIVOT和PIVOT函数来获得所需的结果。如果要将已知数量的agent值转换为列,则可以对查询进行硬编码:

代码语言:javascript
复制
select 
  case when TransactionType is null then 'Total' else [Credit/Debit] end [Credit/Debit],
  case when TransactionType is null then '' else TransactionType end TransactionType,
  Sum([Agent 1]) Agent1, 
  sum([Agent 2]) Agent2
from
(
  select  [Agent], 
    [Credit/Debit], 
    PaymentType as TransactionType, 
    value
  from TableA
  unpivot
  (
    value
    for [Credit/Debit] in ([AmountCredit], [AmountDebit])
  ) unpiv
) src
pivot
(
  sum(value)
  for agent in ([Agent 1], [Agent 2])
) piv
group by GROUPING SETS ([Credit/Debit], TransactionType), ([Credit/Debit]);

参见SQL Fiddle with Demo。

如果您有未知数量的agents,那么您将需要使用动态agents,但不能在视图中使用动态SQL,您必须将代码放在存储过程中。动态SQL将为:

代码语言:javascript
复制
DECLARE @cols AS NVARCHAR(MAX),
    @colSum AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Agent) 
                    from TableA
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colSum = STUFF((SELECT distinct ', Sum(' + QUOTENAME(Agent)+') as ' +QUOTENAME(Agent)
                    from TableA
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'select 
                case when TransactionType is null then ''Total'' else [Credit/Debit] end [Credit/Debit],
                case when TransactionType is null then '''' else TransactionType end TransactionType,
                '+@colSum +'
              from
              (
                select  [Agent], 
                  [Credit/Debit], 
                  PaymentType as TransactionType, 
                  value
                from TableA
                unpivot
                (
                  value
                  for [Credit/Debit] in ([AmountCredit], [AmountDebit])
                ) unpiv
              ) src
              pivot
              (
                sum(value)
                for agent in ('+@cols+')
              ) piv
              group by GROUPING SETS ([Credit/Debit], TransactionType), ([Credit/Debit])'

execute(@query)

参见SQL Fiddle with Demo。查询的结果将是:

代码语言:javascript
复制
| CREDIT/DEBIT | TRANSACTIONTYPE | AGENT 1 | AGENT 2 |
------------------------------------------------------
| AmountCredit |            Cash |      20 |      40 |
| AmountCredit |     Credit Card |      20 |      20 |
|        Total |                 |      40 |      60 |
|  AmountDebit |            Cash |      20 |      40 |
|  AmountDebit |     Credit Card |      10 |      10 |
|        Total |                 |      30 |      50 |
票数 2
EN

Stack Overflow用户

发布于 2013-03-04 22:51:51

听起来像是你想做像这样的事情

代码语言:javascript
复制
CREATE TABLE #Table 
(
    [PayType] VARCHAR(100),
    [Name] VARCHAR(100),
    [Amt1] INT,
    [Amt2] INT
)

INSERT INTO #Table VALUES
('Cash','michaeljackson',1,9),
('Credit','michaeljackson',1,9),
('Cash','jq',10,20),
('Credit','jq',7,9),
('Cash','phil',1,2),
('Credit','phil',3,4),
('Cash','simplesimon',99,1),
('Credit','simplesimon',101,2);


SELECT *
FROM 
    ( 
    SELECT  PayType,
        Name,
        Amt1
    FROM    #Table
    ) AS sourceTable
    PIVOT
    (
    SUM(Amt1) FOR Name IN ("michaeljackson","jq","phil", "simplesimon") 
    ) AS pivotTable;

SELECT *
FROM 
    ( 
    SELECT  PayType,
        Name,
        Amt2
    FROM    #Table
    ) AS sourceTable
    PIVOT
    (
    SUM(Amt2) FOR Name IN ("michaeljackson","jq","phil", "simplesimon") 
    ) AS pivotTable;
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/15202975

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档