T SQL/ Reporting Question I have a view that is pulling sales from multiple companies and I need to basically 'combine' two rows so they display as one.  Here is a picture of what the data looks like:



I need to take all rows for Wine Group and add them into Other/Us Direct and then delete Wine Group or Filter it out so it doesn't show after adding it's values.   I tried writing a case statement and also just tried to aggregate the sum of values and union it to the existing view but it still displays them as separate entities.  Any ideas or tips would be much appreciated!    

Here is current view query that pulls from the table that has this information:

SELECT  [LoadDate]
      ,[Company]
      ,CASE WHEN [ReportGroup] = 'Germany' THEN 'Direct Germany' ELSE ReportGroup END AS ReportGroup
      ,[ShipToCountryCode]
      ,[LLCCustName]
      ,[TodayGrossSalesUSD]
      ,[TodayGrossSalesEURO]
      ,[CurrMonthInvoiceUSD]
      ,[CurrMonthInvoiceEURO]
      ,[CurrMonthShipUSD]
      ,[CurrMonthShipEURO]
      ,[CurrMonthBudgetUSD]
      ,[CurrMonthBudgetEURO]
      ,[CurrMonthLinearUSD]
      ,[CurrMonthLinearEURO]
      ,[LastYearCurrMonthUSD]
      ,[LastYearCurrMonthEURO]
      ,[LastYearCurrMonthLinearUSD]
      ,[LastYearCurrMonthLinearEURO]
      ,[TodayOrderIntakeUSD]
      ,[TodayOrderIntakeEUR]
      ,[TodayVolume]
      ,[CurrMonthInvoiceVolume]
      ,[CurrMonthShipVolume]
      ,[CurrMonthBudgetVolume]
      ,[CurrMonthLinearVolume]
      ,[LastYearCurrMonthVolume]
      ,[LastYearCurrMonthLinearVolume]
      ,[TodayOrderIntakeVolume]
      ,[NextMonthVolume]
      ,[NextMonthGrossSalesUSD]
      ,[NextMonthGrossSalesEURO]
      ,[LastYearCurrMonthAsOfVolume]
      ,[LastYearCurrMonthAsOfUSD]
      ,[LastYearCurrMonthAsOfEURO]
      ,[CurrMonthForecastVolume]
      ,[CurrMonthForecastUSD]
      ,[CurrMonthForecastEURO]
      ,DayType
  FROM [BI_DB].[dbo].[DailySummary]

UNION

SELECT
DISTINCT 
LoadDate,
Company,
CASE WHEN [ReportGroup] = 'Germany' THEN 'Direct Germany' ELSE ReportGroup END AS ReportGroup,
[ShipToCountryCode],
[LLCCustName],
NULL AS [TodayGrossSalesUSD],
NULL AS [TodayGrossSalesEURO],
NULL AS [CurrMonthInvoiceUSD],
NULL AS [CurrMonthInvoiceEURO],
NULL AS [CurrMonthShipUSD],
NULL AS [CurrMonthShipEURO],
[CurrMonthBudgetUSD],
[CurrMonthBudgetEURO],
[CurrMonthLinearUSD],
[CurrMonthLinearEURO],
NULL AS [LastYearCurrMonthUSD],
NULL AS [LastYearCurrMonthEURO],
NULL AS [LastYearCurrMonthLinearUSD],
NULL AS [LastYearCurrMonthLinearEURO],
NULL AS [TodayOrderIntakeUSD],
NULL AS [TodayOrderIntakeEUR],
NULL AS [TodayVolume],
NULL AS [CurrMonthInvoiceVolume],
NULL AS [CurrMonthShipVolume],
[CurrMonthBudgetVolume],
[CurrMonthLinearVolume],
NULL AS [LastYearCurrMonthVolume],
[LastYearCurrMonthLinearVolume],
NULL AS [TodayOrderIntakeVolume],
NULL AS [NextMonthVolume],
NULL AS [NextMonthGrossSalesUSD],
NULL AS [NextMonthGrossSalesEURO],
NULL AS [LastYearCurrMonthAsOfVolume],
NULL AS [LastYearCurrMonthAsOfUSD],
NULL AS [LastYearCurrMonthAsOfEURO],
[CurrMonthForecastVolume],
[CurrMonthForecastUSD],
[CurrMonthForecastEURO],
DayType
  FROM [BI_DB].[dbo].[DailySummaryFullMonth]
WHERE LoadDate &gt; (SELECT MAX(LoadDate) FROM [BI_DB].[dbo].[DailySummary])
This should address the jist of your question.  You will need to check the aggregation to make sure it is appropriate for the data, though at a quick glance everything seemed to be good with SUM or Group.  You may want to replace the CTE with a real table as well.

;WITH
LLCCustNameMapping AS (
    SELECT * FROM (VALUES
        ('Wine Group', 'Other/Us Direct')
    )V(From, To)
)

  SELECT  [LoadDate]
       ,[Company]
       ,CASE WHEN [ReportGroup] = 'Germany' THEN 'Direct Germany' ELSE ReportGroup END AS ReportGroup
       ,[ShipToCountryCode]
       ,IsNull(map.To,  [LLCCustName]) [LLCCustName]
       ,SUM([TodayGrossSalesUSD]) [TodayGrossSalesUSD]
       ,SUM([TodayGrossSalesEURO]) [TodayGrossSalesEURO]
       ,SUM([CurrMonthInvoiceUSD]) [CurrMonthInvoiceUSD]
       ,SUM([CurrMonthInvoiceEURO]) [CurrMonthInvoiceEURO]
       ,SUM([CurrMonthShipUSD]) [CurrMonthShipUSD]
       ,SUM([CurrMonthShipEURO]) [CurrMonthShipEURO]
       ,SUM([CurrMonthBudgetUSD]) [CurrMonthBudgetUSD]
       ,SUM([CurrMonthBudgetEURO]) [CurrMonthBudgetEURO]
       ,SUM([CurrMonthLinearUSD]) [CurrMonthLinearUSD]
       ,SUM([CurrMonthLinearEURO]) [CurrMonthLinearEURO]
       ,SUM([LastYearCurrMonthUSD]) [LastYearCurrMonthUSD]
       ,SUM([LastYearCurrMonthEURO]) [LastYearCurrMonthEURO]
       ,SUM([LastYearCurrMonthLinearUSD]) [LastYearCurrMonthLinearUSD]
       ,SUM([LastYearCurrMonthLinearEURO]) [LastYearCurrMonthLinearEURO]
       ,SUM([TodayOrderIntakeUSD]) [TodayOrderIntakeUSD]
       ,SUM([TodayOrderIntakeEUR]) [TodayOrderIntakeEUR]
       ,SUM([TodayVolume]) [TodayVolume]
       ,SUM([CurrMonthInvoiceVolume]) [CurrMonthInvoiceVolume]
       ,SUM([CurrMonthShipVolume]) [CurrMonthShipVolume]
       ,SUM([CurrMonthBudgetVolume]) [CurrMonthBudgetVolume]
       ,SUM([CurrMonthLinearVolume]) [CurrMonthLinearVolume]
       ,SUM([LastYearCurrMonthVolume]) [LastYearCurrMonthVolume]
       ,SUM([LastYearCurrMonthLinearVolume]) [LastYearCurrMonthLinearVolume]
       ,SUM([TodayOrderIntakeVolume]) [TodayOrderIntakeVolume]
       ,SUM([NextMonthVolume]) [NextMonthVolume]
       ,SUM([NextMonthGrossSalesUSD]) [NextMonthGrossSalesUSD]
       ,SUM([NextMonthGrossSalesEURO]) [NextMonthGrossSalesEURO]
       ,SUM([LastYearCurrMonthAsOfVolume]) [LastYearCurrMonthAsOfVolume]
       ,SUM([LastYearCurrMonthAsOfUSD]) [LastYearCurrMonthAsOfUSD]
       ,SUM([LastYearCurrMonthAsOfEURO]) [LastYearCurrMonthAsOfEURO]
       ,SUM([CurrMonthForecastVolume]) [CurrMonthForecastVolume]
       ,SUM([CurrMonthForecastUSD]) [CurrMonthForecastUSD]
       ,SUM([CurrMonthForecastEURO]) [CurrMonthForecastEURO]
       ,DayType
  FROM [BI_DB].[dbo].[DailySummary] s
    LEFT JOIN LLCCustNameMapping map ON s.LLCCustName = map.From
  WHERE [LLCCustName] IN ('Wine Group', 'Other/Us Direct')
  GROUP BY [LoadDate]
       ,[Company]
       ,CASE WHEN [ReportGroup] = 'Germany' THEN 'Direct Germany' ELSE ReportGroup END AS ReportGroup
       ,[ShipToCountryCode]
       ,IsNull(map.To,  [LLCCustName]) [LLCCustName]
       ,DayType