Libin Jose Libin Jose - 3 months ago 12
SQL Question

Pivot Dynamic Columns on SQL Server

I have 3 tables like these,

1: LoanPrograms

id Name
------------------------------------ --------------------------------
9C374820-357B-46FE-B509-003B9DB1507F FHA 30 YEAR FIXED HIGH BALANCE
B8A76A63-D867-4D89-9749-018238C446B5 JUMBO 10/1 YR ARM BARCLAYS
458AA688-AF12-438D-AFA3-01C804E9C6F6 JUMBO EXPANDED PLUS 30 YEAR FIXED
D8D6D621-6D14-493B-AAA8-02285EC9A54D z_Test_FHA_30_Year_SL
8C735061-3CA2-4065-ABBF-03CF4234BD36 CONF 15 YR 105 DU REFI PLUS


2: Channels

Id Name
------------------------------------ -------------
DCBAD5E8-91B1-4EC6-8545-4265367EFFCD Broker
0BC94231-C174-41DE-A9C5-980C7834B55A Correspond
6000F785-0D98-4DA6-8431-B13F01295EFE Retail


3: LoanProgramChannels

Loanprogram_Id Channel_id
------------------------------------ ------------------------------------
9C374820-357B-46FE-B509-003B9DB1507F DCBAD5E8-91B1-4EC6-8545-4265367EFFCD
9C374820-357B-46FE-B509-003B9DB1507F 0BC94231-C174-41DE-A9C5-980C7834B55A
458AA688-AF12-438D-AFA3-01C804E9C6F6 0BC94231-C174-41DE-A9C5-980C7834B55A
D8D6D621-6D14-493B-AAA8-02285EC9A54D 6000F785-0D98-4DA6-8431-B13F01295EFE


I have to display the result as below. List all loan programs and if the loan program has entry for a particular channel in 'LoanProgramChannel' table mark it as "Y" else as "N". I am quite new to SQL , I know I have to use pivot function to achieve these. But not sure how can I implement that?

Loan Program Name Broker Correspond Retail
-------------------- ------- --------- -------
FHA 30 YEAR FIXED HIGH BALANCE y y N
JUMBO 10/1 YR ARM BARCLAYS N N N
JUMBO EXPANDED PLUS 30 YEAR FIXED N y N
z_Test_FHA_30_Year_SL N N Y
CONF 15 YR 105 DU REFI PLUS N N N

Answer

Please try this query. It uses SQL PIVOT along with a simple ISNULL check for getting false values

    SELECT 
        [Loan Program Name],
        ISNULL([Broker],'N') as [Broker],
        ISNULL([Correspond],'N') as [Correspond],
        ISNULL([Retail],'N') as [Retail]
    FROM
        (
            SELECT 
                L.Name as [Loan Program Name],
                'Y' as v,C.Name  as [ChannelType] 
            FROM LoanPrograms L LEFT JOIN LoanProgramChannels LC 
                ON L.id =LC.Loanprogram_Id  LEFT JOIN Channels C 
                ON C.id=LC.Channel_id
        ) S
        PIVOT
        (
            MAX(v) FOR [ChannelType] IN ([Broker],[Correspond],[Retail])
        )piv

I also create a fiddle : http://sqlfiddle.com/#!6/3e6c6/2