sql server - SQL - Case When on same row

Question

I have a problem with a query that involves multiple case whens

     SELECT DISTINCT
            ,-1*(SUM(ISNULL(DB.[Commission_Bal],0)) + SUM(ISNULL(UP.AMOUNT,0))) AS [Commission_Bal]
            ,DATEDIFF(DAY,DB_CD.Create_Date,'20170123')  AS Days_In_Neg_Bal
            ,CASE WHEN DOC.DocumentTypeCode = 'MCS30' THEN DOC.DocumentDate END AS '30 Day Letter'
            ,CASE WHEN DOC.DocumentTypeCode = 'MCS60' THEN DOC.DocumentDate END AS '60 Day Letter'
            ,CASE WHEN DOC.DocumentTypeCode = 'MCS90' THEN DOC.DocumentDate END AS '90 Day Letter'
            ,CASE WHEN DOC.DocumentTypeCode = 'MCS20' THEN DOC.DocumentDate END AS '120 Day Letter'
            ,DB.[Cycle_Date]

It returns separate rows but i would like the result to have them on the same row. It returns the top image but i would like it to return results similar to the bottom image

Answer 1

Make it a conditional aggregation

SELECT  (-1*(SUM(ISNULL(DB.[Commission_Bal],0)) + SUM(ISNULL(UP.AMOUNT,0))))/count(*) AS [Commission_Bal]
       ,DATEDIFF(DAY,DB_CD.Create_Date,'20170123')  AS Days_In_Neg_Bal
       ,Max(CASE WHEN DOC.DocumentTypeCode = 'MCS30' THEN DOC.DocumentDate END) AS '30 Day Letter'
       ,Max(CASE WHEN DOC.DocumentTypeCode = 'MCS60' THEN DOC.DocumentDate END) AS '60 Day Letter'
       ,Max(CASE WHEN DOC.DocumentTypeCode = 'MCS90' THEN DOC.DocumentDate END) AS '90 Day Letter'
       ,Max(CASE WHEN DOC.DocumentTypeCode = 'MCS20' THEN DOC.DocumentDate END) AS '120 Day Letter'
       ,DB.[Cycle_Date]
 From  YourTable
 Group By -1*(SUM(ISNULL(DB.[Commission_Bal],0)) + SUM(ISNULL(UP.AMOUNT,0)))
         ,DATEDIFF(DAY,DB_CD.Create_Date,'20170123')
         ,DB.[Cycle_Date]

EDIT - Option 2:

Select [Commission_Bal]
      ,[Days_In_Neg_Bal]
      ,[30 Day Letter]  = max([30 Day Letter])
      ,[60 Day Letter]  = max([60 Day Letter])
      ,[60 Day Letter]  = max([90 Day Letter])
      ,[120 Day Letter] = max([120 Day Letter])
      ,[Cycle_Date]
 From (
         SELECT DISTINCT
                     -1*(SUM(ISNULL(DB.[Commission_Bal],0)) + SUM(ISNULL(UP.AMOUNT,0))) AS [Commission_Bal]
                    ,DATEDIFF(DAY,DB_CD.Create_Date,'20170123')  AS Days_In_Neg_Bal
                    ,CASE WHEN DOC.DocumentTypeCode = 'MCS30' THEN DOC.DocumentDate END AS '30 Day Letter'
                    ,CASE WHEN DOC.DocumentTypeCode = 'MCS60' THEN DOC.DocumentDate END AS '60 Day Letter'
                    ,CASE WHEN DOC.DocumentTypeCode = 'MCS90' THEN DOC.DocumentDate END AS '90 Day Letter'
                    ,CASE WHEN DOC.DocumentTypeCode = 'MCS20' THEN DOC.DocumentDate END AS '120 Day Letter'
                    ,DB.[Cycle_Date]
          From  YourTable
       ) A
 Group By [Commission_Bal]
         ,[Days_In_Neg_Bal]
         ,[Cycle_Date]

EDIT 3 - Full Final Query

Select [ProcessingCompany]
      ,[External_ID]
      ,[BrokerName]
      ,[AGENT_ADDRESS]
      ,[Commission_Bal]
      ,[Days_In_Neg_Bal]
      ,[30 Day Letter]  = max([30 Day Letter])
      ,[60 Day Letter]  = max([60 Day Letter])
      ,[60 Day Letter]  = max([90 Day Letter])
      ,[120 Day Letter] = max([120 Day Letter])
      ,[Cycle_Date]
 From (

    SELECT DISTINCT
           db.ProcessingCompany
          ,DB.[External_ID]
          ,DB.BrokerName
          ,DB.AGENT_ADDRESS
          ,-1*(SUM(ISNULL(DB.[Commission_Bal],0)) + SUM(ISNULL(UP.AMOUNT,0))) AS [Commission_Bal]
          ,DATEDIFF(DAY,DB_CD.Create_Date,'20170123')  AS Days_In_Neg_Bal
          ,CASE WHEN DOC.DocumentTypeCode = 'MCS30' THEN DOC.DocumentDate END AS '30 Day Letter'
          ,CASE WHEN DOC.DocumentTypeCode = 'MCS60' THEN DOC.DocumentDate END AS '60 Day Letter'
          ,CASE WHEN DOC.DocumentTypeCode = 'MCS90' THEN DOC.DocumentDate END AS '90 Day Letter'
          ,CASE WHEN DOC.DocumentTypeCode = 'MCS20' THEN DOC.DocumentDate END AS '120 Day Letter'
          ,DB.[Cycle_Date]
        FROM     
            debit_balance_final AS DB
        LEFT JOIN up_debit AS UP
            ON UP.GroupID = DB.[Processing_Group_Code] 
            AND UP.PAY_ENTITY_ID = DB.[External_ID]
        LEFT JOIN docs AS DOC
            ON DOC.RepMAN = DB.[External_ID]

    INNER JOIN 
        (
            SELECT DB2.EXTERNAL_ID,

                DB2.Processing_Group_Code ,
                MIN(DB2.Create_Date) AS CREATE_DATE

            FROM MCS_Debit_Balance DB2 WITH (NOLOCK)  
            WHERE DB2.CREATE_DATE  > ( SELECT ISNULL(MAX(CREATE_DATE),'1/1/1900') 
                                       FROM MCS_DEBIT_BALANCE MDB 
                                       WHERE MDB.EXTERNAL_ID = DB2.External_ID 
                                       AND DB2.Processing_Group_Code = MDB.Processing_Group_Code 
                                       AND MDB.Commission_Bal = 0)

            GROUP BY DB2.External_ID,DB2.Processing_Group_Code

        )  DB_CD 
                ON DB.External_ID = DB_CD.External_ID 
                AND DB.Processing_Group_Code = DB_CD .Processing_Group_Code  

    --      WHERE DB.[External_ID] = '1002937'

            GROUP BY

            db.ProcessingCompany
            ,DB.[Business_Unit_Ap]
            ,DB.[External_ID]
            ,DB.[Processing_Group_Code]
            ,DB.BrokerName
            ,DB.AGENT_ADDRESS
            ,CASE WHEN DOC.DocumentTypeCode = 'MCS30' THEN DOC.DocumentDate END
            ,CASE WHEN DOC.DocumentTypeCode = 'MCS60' THEN DOC.DocumentDate END
            ,CASE WHEN DOC.DocumentTypeCode = 'MCS90' THEN DOC.DocumentDate END
            ,CASE WHEN DOC.DocumentTypeCode = 'MCS20' THEN DOC.DocumentDate END
            ,DB.[Balance_Year]
            ,DATEDIFF(DAY,DB_CD.Create_Date,'20170123')
    --      ,DOC.DocumentTypeID
            ,DB.[Cycle_Date] 

) A
 Group By [ProcessingCompany]
         ,[External_ID]
         ,[BrokerName]
         ,[AGENT_ADDRESS]
         ,[Commission_Bal]
         ,[Days_In_Neg_Bal]
         ,[Cycle_Date]

ORDER BY DB.[External_ID]