代码之家 › 专栏 › 技术社区 › Billy Coover

SQL条件透视

pivot sql-server sql

Billy Coover · 技术社区 · 15 年前

是的,这是另一个关键问题…我已经看完了几乎所有之前的问题,我似乎无法把一个能满足我需要的查询拼凑起来。

我的桌子是这样的:

FirmName    Account Balance Pmt Revolving   Installment     Mortgage
Amex    12345   10000   2000    1   0   0
Discover    54321   20000   4000    1   0   0
Chase   13579   100000  1500    0   0   1
Wells Fargo 2468    40000   900 0   1   0

最后三位列(旋转、分期付款和抵押)指示如何将列卷成类型。每个结果需要基于类型及其行计数的三列。结果应该是一行多列。结果应该是这样的:

Revolving1_Firm Revolving1_Balance  Revolving1_Pmt  Revolving2_Firm    Revolving2_Balance   Revolving2_Pmt  Realestate1_Firm    Realestate1_Balance     Realestate1_Pmt Vehicle1_Firm   Vehicle1_Balance    Vehicle1_Pmt

Amex        10000   2000    Discover    20000   4000    Chase   100000  1500    Wells Fargo 40000   900

如何基于位字段(旋转、分期付款和抵押)进行旋转并保留正确的计数,以便每一列都被追加计数?

1 回复 | 直到 15 年前

Cade Roux 15 年前

这必须用动态sql来完成。首先,您需要确定整个填充中每种类型的最大数量(可能还需要将3位列转换为单个loan_type列,因为这是您的分区),然后在规范化数据上(按firmname的loan_type order进行分区)使用row_number()与之相关,以便将事情放在其右栏中。

我不得不问在数据库中这样做有多重要——因为模式没有被修复,所以很难看到这样做的实用性。

不过,只比大多数动态轴心稍微复杂一点,所以如果上面的提示不能让您理解,而且您仍然希望我尝试一下,我稍后将尝试发布一些实际的工作代码。

SET NOCOUNT ON

DECLARE @t AS TABLE
    (
     FirmName varchar(50) NOT NULL
    ,Account varchar(50) NOT NULL
    ,Balance money NOT NULL
    ,Pmt money NOT NULL
    ,Revolving bit NOT NULL
    ,Installment bit NOT NULL
    ,Mortgage bit NOT NULL
    ) ;
INSERT  INTO @t
VALUES  ('Amex', '12345', 10000, 2000, 1, 0, 0) ;
INSERT  INTO @t
VALUES  ('Discover', '54321', 20000, 4000, 1, 0, 0) ;
INSERT  INTO @t
VALUES  ('Chase', '13579', 100000, 1500, 0, 0, 1) ;
INSERT  INTO @t
VALUES  ('Wells Fargo', '2468', 40000, 900, 0, 1, 0) ;

WITH    n1
          AS (
              SELECT    FirmName
                       ,Account
                       ,Balance
                       ,Pmt
                       ,LoanType
                       ,LoanTypeFlag
              FROM      @t UNPIVOT ( LoanTypeFlag FOR LoanType IN ([Revolving], [Installment], [Mortgage]) ) AS unpvt
             ),
        n2
          AS (
              SELECT    FirmName
                       ,Balance
                       ,Pmt
                       ,LoanType
              FROM      n1
              WHERE     LoanTypeFlag = 1
             ),
        n3
          AS (
              SELECT    FirmName
                       ,Balance
                       ,Pmt
                       ,LoanType
                       ,ROW_NUMBER() OVER (PARTITION BY LoanType ORDER BY FirmName) AS SequenceNumber
              FROM      n2
             ),
        n4
          AS (
              SELECT    LoanType + CONVERT(varchar, SequenceNumber) AS Column_Prefix
                       ,FirmName AS Firm
                       ,CONVERT(varchar(50), Balance) AS Balance
                       ,CONVERT(varchar(50), Pmt) AS Pmt
              FROM      n3
             ),
        n5
          AS (
              SELECT    Column_Prefix + '_' + Col AS Col_Nm
                       ,Val
              FROM      n4 UNPIVOT ( Val FOR Col IN ([Firm], [Balance], [Pmt]) ) AS unpvt
             )
    SELECT  *
    FROM    n5 PIVOT ( MAX(Val) FOR Col_Nm IN ([Installment1_Firm], [Installment1_Balance], [Installment1_Pmt],
                                               [Mortgage1_Firm], [Mortgage1_Balance], [Mortgage1_Pmt], [Revolving1_Firm],
                                               [Revolving1_Balance], [Revolving1_Pmt], [Revolving2_Firm],
                                               [Revolving2_Balance], [Revolving2_Pmt]) ) AS pvt

剩下的主要问题是最后一个pivot列表(可以像我提到的那样动态地生成这个列表)和类型安全性,因为在最后一个pivot之前的entity-value阶段,所有内容都符合varchar(50)。

另外,如果位标志不是互斥的,则会有一些重复项…

我认为,如果您使用的表单生成系统的最终透视表列表相对固定,因此您可以不使用动态sql来生成该列表,但这会使系统稍微不适合将来使用。

这将生成pivot_列表(可以简化):

WITH    n1
          AS (
              SELECT    FirmName
                       ,Account
                       ,Balance
                       ,Pmt
                       ,LoanType
                       ,LoanTypeFlag
              FROM      @t UNPIVOT ( LoanTypeFlag FOR LoanType IN ([Revolving], [Installment], [Mortgage]) ) AS unpvt
             ),
        n2
          AS (
              SELECT    FirmName
                       ,Balance
                       ,Pmt
                       ,LoanType
              FROM      n1
              WHERE     LoanTypeFlag = 1
             ),
        n3
          AS (
              SELECT    FirmName
                       ,Balance
                       ,Pmt
                       ,LoanType
                       ,ROW_NUMBER() OVER (PARTITION BY LoanType ORDER BY FirmName) AS SequenceNumber
              FROM      n2
             ),
        n4
          AS (
              SELECT    LoanType + CONVERT(varchar, SequenceNumber) AS Column_Prefix
                       ,FirmName AS Firm
                       ,CONVERT(varchar(50), Balance) AS Balance
                       ,CONVERT(varchar(50), Pmt) AS Pmt
              FROM      n3
             ),
        n5
          AS (
              SELECT    Column_Prefix + '_' + Col AS Col_Nm
                       ,Val
              FROM      n4 UNPIVOT ( Val FOR Col IN ([Firm], [Balance], [Pmt]) ) AS unpvt
             ),
        pivot_list(pivot_list)
          AS (
              SELECT    ',' + QUOTENAME(Col_Nm)
              FROM      n5
              FOR       XML PATH('')
             )
    SELECT  STUFF(pivot_list, 1, 1, '') AS pivot_list
    FROM    pivot_list