SQL conditional Pivot

Question

Yes, this is another Pivot question... I've read through nearly all the previous questions and I can't seem to hack together a query that does what I need.

Here is what my table looks like:

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

The last three bit columns (Revolving, Installment, & Mortgage) dictate how the columns should be rolled up into a type. Each result requires three columns based on the type and its row count. The outcome should be one row with many columns. Here is what the result should look like:

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

How do you pivot based on the bit fields (Revolving, Installment, & Mortgage) and retain the proper count so that that each column gets count # appended to it?

Answer 1

This would have to be done with dynamic SQL. First you would need to determine the maximum number of each type (and probably also convert the 3 bit columns into a single LOAN_TYPE column since that's your partition) over your whole population and then use a ROW_NUMBER() OVER (PARTITION BY LOAN_TYPE ORDER BY FirmName) over the normalized data correlated with that in order to put things in their right column.

I'd have to ask how important it is to do this in the database - because the schema is not fixed, it's pretty difficult to see the utility of doing it this way.

Still, only a little more complex than most dynamic pivots, so if the hints above don't get you there, and you still want me to take a stab at it, I'll try to post some actual working code later.

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

Your main remaining problem is the final PIVOT list (you could generate this dynamically as I mentioned) and the type-safety because everything is conformed to varchar(50) in the entity-value stage before the final PIVOT.

Also, if the bit flags are not mutually exclusive, you will have some duplicates...

I would think if you are using a form generation system that the final PIVOT list is relatively fixed, so you could leave off the dynamic SQL to generate that list, but it would make the system slightly non-future-proof.

This will generate the pivot_list (could be simplified):

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