SQL conditional Pivot
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:
开发者_如何学JAVAFirmName 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?
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
精彩评论