时间戳

首页 > 解决方案 > 如何使用 sql 创建数据透视表

sql - 如何使用 sql 创建数据透视表

问题描述

我是 SQL 新手，我有一张这样的表。

以下是查询：

select 
    gc.GC_Name, dt.GC_SectorType, dt.ageing,
    sum(cast(dt.[Brokerage Debtors] as numeric)) as Brokerage_Amt,
    dt.divisionalofficename  
from
    [AR].[Fact_Brokerage_Debt] dt 
inner join 
    AUM.DIM_BUSINESS_TYPE BT on BT.Business_Type_WId_PK = dt.BusinessType_WID 
inner join 
    aum.Dim_GroupCompany gc on dt.insurer_Wid = gc.GC_WID
where 
    bt.Business_Type_Wid in (4, 8, 10) 
    and dt.ageing <> '<30' 
    and cast(dt.[Brokerage Debtors] as numeric) > 0 
    and gc.GC_SectorType = 'psu'
group by  
    gc.GC_Name, dt.GC_SectorType, dt.ageing, dt.divisionalofficename

[sql_table]

1

我被告知要获取这样的数据

[请求格式]2

Grandtotal是基于 的总数brockrage_amt。

我知道我需要使用 PIVOT 功能。但是不能清楚的理解。如果有人可以在上述情况下解释它，那将是很大的帮助。（或任何替代方案，如果有的话）

标签： sqltsql

解决方案

我假设总计基于保险名称和 DO 代码，总计是总和而不是计数。查询中的字段名称、sql_table 和 request_format 之间也存在一些差异。下面的示例代码必须根据您的特定情况进行调整，但它是您要求的基本结构和格式。

此外，您不会得到确切的 request_format，因为查询结果不会有颜色、格式等...

这是一个包含示例数据的工作示例：

DECLARE @testdata TABLE
    (
        [Insurance_Name] VARCHAR(100)
      , [DO_Code] VARCHAR(100)
      , [ageing] VARCHAR(10)
      , [Brokerage_Amt] INT
    );

INSERT INTO @testdata (
                          [Insurance_Name]
                        , [DO_Code]
                        , [ageing]
                        , [Brokerage_Amt]
                      )
VALUES ( 'Insurance Company 1', '123', '31-60', 100 )
     , ( 'Insurance Company 1', '123', '91-120', 200 )
     , ( 'Insurance Company 1', '123', '>=365', 300 )
     , ( 'Insurance Company 1', '234', '61-90', 300 )
     , ( 'Insurance Company 1', '234', '61-90', 300 )
     , ( 'Insurance Company 1', '234', '121-180', 300 )
     , ( 'Insurance Company 1', '234', '181-364', 200 )
     , ( 'Insurance Company 2', '789', '61-90', 50 )
     , ( 'Insurance Company 2', '789', '121-180', 25 )
     , ( 'Insurance Company 2', '789', '181-364', 9 );

SELECT [pvt].[Insurance_Name]
     , [pvt].[DO_Code]
     , [31-60]
     , [61-90]
     , [91-120]
     , [121-180]
     , [181-364]
     , [>=365]
     , [pvt].[GrandTotal]
FROM   (
           SELECT [Insurance_Name]
                , [DO_Code]
                , [ageing]
                , [Brokerage_Amt]
                , SUM([Brokerage_Amt]) OVER ( PARTITION BY [Insurance_Name]
                                                         , [DO_Code]
                                            ) AS [GrandTotal] --here we determine that grand total based on the Insurance_Name and DO_Code
           FROM   @testdata
       ) AS [ins]
PIVOT (
          SUM([Brokerage_Amt]) --aggregate and pivot this column
          FOR [ageing] --sum the above and make column where the value is one of these [31-60], [61-60], etc...
          IN ( [31-60], [61-90], [91-120], [121-180], [181-364], [>=365] )
      ) AS [pvt];

为您提供以下结果：

Insurance_Name            DO_Code    31-60       61-90       91-120      121-180     181-364     >=365       GrandTotal
------------------------  ---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Insurance Company 1       123        100         NULL        200         NULL        NULL        300         600
Insurance Company 1       234        NULL        600         NULL        300         200         NULL        1100
Insurance Company 2       789        NULL        50          NULL        25          9           NULL        84

没有示例数据，所以我想尝试重新拟合您的查询将是这样的：

 SELECT [pvt].[Insurance_Name]
     , [pvt].[DO_Code]
     , [31-60]
     , [61-90]
     , [91-120]
     , [121-180]
     , [181-364]
     , [>=365]
     , [pvt].[GrandTotal]
FROM   (
           SELECT     [gc].[GC_Name] AS [Insurance_Name]
                    , [dt].[GC_SectorType] AS [DO_Code]
                    , [dt].[ageing]
                    --, SUM(CAST([dt].[Brokerage Debtors] AS NUMERIC)) AS [Brokerage_Amt]
                    , CAST([dt].[Brokerage Debtors] AS NUMERIC) AS [Brokerage_Amt]
                    , SUM(CAST([dt].[Brokerage Debtors] AS NUMERIC)) OVER (PARTITION BY [gc].[GC_Name], [dt].[GC_SectorType]) AS GrandTotal
                    , [dt].[divisionalofficename]
           FROM       [AR].[Fact_Brokerage_Debt] [dt]
           INNER JOIN [AUM].[DIM_BUSINESS_TYPE] [BT]
               ON [BT].[Business_Type_WId_PK] = [dt].[BusinessType_WID]
           INNER JOIN [aum].[Dim_GroupCompany] [gc]
               ON [dt].[insurer_Wid] = [gc].[GC_WID]
           WHERE      [BT].[Business_Type_Wid] IN ( 4, 8, 10 )
                      AND [dt].[ageing] <> '<30'
                      AND CAST([dt].[Brokerage Debtors] AS NUMERIC) > 0
                      AND [gc].[GC_SectorType] = 'psu'
       --I guess you would not need the sum and group by, sum should be hanlded in the pivot, but above we add a sum partioning by [gc].[GC_Name], [dt].[GC_SectorType] for the grand total
       --GROUP BY   [gc].[GC_Name]
       --         , [dt].[GC_SectorType]
       --         , [dt].[ageing]
       --         , [dt].[divisionalofficename];
       ) AS [ins]
PIVOT (
          SUM([Brokerage_Amt]) --aggregate and pivot this column
          FOR [ageing] --sum the above and make column where the value is one of these [31-60], [61-60], etc...
          IN ( [31-60], [61-90], [91-120], [121-180], [181-364], [>=365] )
      ) AS [pvt];

我推测需要进行更改，因为没有提供示例数据和表定义。由于我无法运行查询，因此可能存在拼写错误。

推荐阅读