SQL Crosstab with undetermined columns

Question

I see a lot of similar questions, but almost all of them wind up grouping results as column names (Column names based on results), mine is a more simple list. I don't care if it uses dynamic SQL or not (I'd think it has to).

Please don't tell me I need to restructure the tables, I'm working from a legacy system and don't have that option.

Basically, I just need a list of all valid table "B" entries that match a given record from table "A", in a row.

I don't have any code sample yet, because I'm not seeing a way to set this up correctly.

Table: Customer c
CustID   Name
1        Bill Smith
2        Jim Jones
3        Mary Adams
4        Wendy Williams

Table: Debt d
CustID   Creditor             Balance
1        ABC Loans            245
1        Citibank             815
2        Soprano Financial   74000
3        Citibank             24
3        Soprano Financial   93000
3        Wells Fargo          275
3        Midwestern S&L       2500
4        ABC Loans            1500
4        Fred's Payday Loan   1000

Desired Output:
Name            Cred1                Bal1   Cred2               Bal2   Cred3        Bal3   Cred4            Bal4
Bill Smith      ABC Loans            245    Citibank            815    (NULL)       (NULL) (NULL)           (NULL)
Jim Jones       Soprano Financial    74000  (NULL)              (NULL) (NULL)       (NULL) (NULL)           (NULL)
Mary Adams      Citibank             24     Soprano Finanacial  93000  Wells Fargo   275   Midwestern S&L   2500
Wendy Williams  ABC Loans            1500   Fred's Payday Loan  1000   (NULL)       (NULL) (NULL)           (NULL)

Basically, I probably have to collect some kind of count of the most number of records for any specific "CustomerID", and define the output columns based on that. If this has already been answered, feel free to link and close this out, I did not see this specific scenario when I did my search.

Answer 1

Here is another dynamic approach. We use Row_Number() to create the minimal number of columns.

Example

Declare @SQL varchar(max) = Stuff((Select Distinct ','+QuoteName(concat('Cred',ColNr)) 
                                                  +','+QuoteName(concat('Bal',ColNr)) 
                                    From (Select ColNr=Row_Number() over (Partition By CustID Order By Creditor) From Debt ) A  
                                    Order By 1 
                                    For XML Path('')),1,1,'') 

Select  @SQL = '
Select *
From (
        Select C.Name
              ,B.*
         From (
                Select *,ColNr=Row_Number() over (Partition By CustID Order By Creditor)
                 From  Debt
              ) A
         Cross Apply (values (concat(''Cred'',ColNr),[Creditor])
                            ,(concat(''Bal'' ,ColNr) ,cast(Balance as varchar(25)))
                     ) B (Item,Value)
         Join Customer C on A.CustID=C.CustID

    ) A
Pivot (max([Value]) For [Item] in (' + @SQL + ') ) p'
--Print @SQL
Exec(@SQL);

Returns

If if Helps, the Generated SQL Looks Like This:

Select *
From (
        Select C.Name
              ,B.*
         From (
                Select *,ColNr=Row_Number() over (Partition By CustID Order By Creditor)
                 From  Debt
              ) A
         Cross Apply (values (concat('Cred',ColNr),[Creditor])
                            ,(concat('Bal' ,ColNr) ,cast(Balance as varchar(25)))
                     ) B (Item,Value)
         Join Customer C on A.CustID=C.CustID

    ) A
Pivot (max([Value]) For [Item] in ([Cred1],[Bal1],[Cred2],[Bal2],[Cred3],[Bal3],[Cred4],[Bal4]) ) p

Just for the Visualization, the query "feeding" the Pivot generates:

Answer 2

I will guess you are already know how to use cross tab so you only need to prepare your data to use it.

STEP 1: Join both tables:

 SELECT c.Name, d.Creditor, d.Balance
 FROM Customer c
 JOIN Debt d
   ON c.CustID = d.CustID

STEP 2: Include a row number to each element related to the customer you are going to use to cross tab

SELECT c.Name, d.Creditor, d.Balance, 
       ROW_NUMBER() over (PARTITION BY Name ORDER BY creditor) as rndebt_tab
FROM Customer c
JOIN Debt d
  ON c.CustID = d.CustID

Now you have:

CustID   Creditor             Balance    rn
1        ABC Loans            245        1      
1        Citibank             815        2
2        Soprano Financial   74000       1
3        Citibank             24         1
3        Soprano Financial   93000       2
3        Wells Fargo          275        3
3        Midwestern S&L       2500       4
4        ABC Loans            1500       1
4        Fred's Payday Loan   1000       2

STEP 3: Create the SOURCE for the cross tab

WITH cte as (
     <query from step2>
)
SELECT Name, 
       'CREDITOR_' + RIGHT('000' + CAST(rn AS VARCHAR(3)),3) as cross_tab,
       Creditor as Value
FROM cte  
UNION all
SELECT Name, 
       'DEBT_' + RIGHT('000' + CAST(rn AS VARCHAR(3)),3) as cross_tab,
       CAST(Balance as VARCHAR(max)) as Value      
FROM cte  

Now you have:

CustID   cross_tab          Value             
1        CREDITOR_001       ABC Loans         
1        CREDITOR_002       Citibank          
2        CREDITOR_001       Soprano Financial 
3        CREDITOR_001       Citibank          
3        CREDITOR_002       Soprano Financial 
3        CREDITOR_003       Wells Fargo       
3        CREDITOR_004       Midwestern S&L    
4        CREDITOR_001       ABC Loans         
4        CREDITOR_002       Fred's Payday Loan
1        DEBT_001           245  
1        DEBT_002           815  
2        DEBT_001       `   74000
3        DEBT_001           24   
3        DEBT_002           93000
3        DEBT_003           275  
3        DEBT_004           2500 
4        DEBT_001           1500 
4        DEBT_002           1000 

EDIT: I use CustID instead of Name on the example but too lazy to change now.