Branch Monthly Membership
Buyer Monthly Membership
Carrying Charge

Company                                         Invoice Line
Id     CompanyName   MembershipType  BranchName Description
20     DeluxPainting BuyerMemberShip Branch1  Branch MonthlyMembership
20     DeluxPainting BuyerMemberShip Branch1  Buyer Monthly Membership
20     Deluxainting  BuyerMemberShip Branch1  Carrying Charge
20     DeluxPainting BuyerMemberShip Branch2  Branch MonthlyMembership
20     DeluxPainting BuyerMemberShip Branch2  Buyer Monthly Membership
20     DeluxPainting BuyerMemberShip Branch2  Carrying Charge
20     DeluxPainting BuyerMemberShip Branch3  Branch MonthlyMembership
20     DeluxPainting BuyerMemberShip Branch3  Buyer Monthly Membership
20     DeluxPainting BuyerMemberShip Branch3  Carrying Charge

Company                                         Invoice Line
Id     CompanyName   MembershipType  BranchName Description
20     DeluxPainting BuyerMemberShip Branch1  Buyer Monthly Membership
20     Deluxainting  BuyerMemberShip Branch1  Carrying Charge
20     DeluxPainting BuyerMemberShip Branch1  Branch MonthlyMembership
20     DeluxPainting BuyerMemberShip Branch2  Branch MonthlyMembership
20     DeluxPainting BuyerMemberShip Branch3  Branch MonthlyMembership

 BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
with PCTE AS 
(
SELECT  Count(*) as Count, Companies.Id as companyid, Branches.id as branchid, c.name as InventoryCenter
FROM            dbo.InventoryItems INNER JOIN
dbo.inventoryCategories on InventoryItems.inventoryCategoryID = inventoryCategories.id Inner JOIN
dbo.InventoryCenters c on inventoryCategories.InventoryCenterId = c.Id INNER JOIN
dbo.Branches ON InventoryItems.BranchId = Branches.Id INNER JOIN
dbo.Companies ON Branches.CompanyId = Companies.Id
WHERE 
InventoryItems.Deleted = 'False'AND
Branches.IsDeleted= 'False' AND
Companies.IsDeleted = 'False'  AND
IsShownToMembers = 'True' AND
Companies.Id = 20 
group by Companies.Id, Branches.id , c.name 
)
, CompanySummaryCTE AS
(
select companyid, branchid, [Part Center], [Equipment Center], [Attachment Center], [Powertrain Center], [Salvage Center] 
from PCTE
PIVOT (Sum(Count) for InventoryCenter IN ([Part Center], [Equipment Center], [Attachment Center], [Powertrain Center], [Salvage Center] )) as P
)
, cteInvoices AS (
select t.CompanyId, t.FirstRun, t.NextRun, f.DisplayedAs as Frequency
, l.Description as InvoiceLineDescription, l.Quantity as InvoiceLineQuantity, l.DiscountAmount as InvoiceLineDiscount
, l.ServiceId as InvoiceLineServiceId
from RecurringInvoiceTemplates t
inner join RecurringInvoiceFrequencies f on t.RecurringInvoiceFrequencyId = f.Id
inner join RecurringInvoiceLines l on l.RecurringInvoiceTemplateId = t.Id
)
, cteFinal AS (
SELECT Companies.Id as CompanyId, Companies.Name AS CompanyName, p.Name as MembershipType,Branches.Name as BranchName, I.InvoiceLineDescription
FROM   
CompanySummaryCTE cte
JOIN Companies ON cte.companyid = companies.Id
JOIN Branches on Branches.CompanyId = Companies.Id
JOIN CompanyProducts cp on companies.id	= cp.companyid
JOIN Products p on cp.ProductId = p.Id
LEFT JOIN cteInvoices I on Companies.Id = i.CompanyId
where 
Companies.IsDeleted = 0
and Branches.IsDeleted = 0
and p.ProductTypeId = 2
) 
--Original data with magic numbers added based on partition functions
SELECT DISTINCT * , 
DENSE_RANK() OVER (PARTITION BY BranchName ORDER BY BranchName, InvoiceLineDescription ) DescriptionGroup,
DENSE_RANK() OVER (PARTITION BY InvoiceLineDescription ORDER BY BranchName) BranchGroup
FROM cteFinal
WHERE 1=1
order by CompanyId 
--Insert into 
-- Once you have the magic numbers worked out, just filter out the rows you don't want
SELECT * FROM (
SELECT * , 
ROW_NUMBER() OVER (PARTITION BY BranchName ORDER BY BranchName, InvoiceLineDescription ) DescriptionGroup,
ROW_NUMBER() OVER (PARTITION BY InvoiceLineDescription ORDER BY BranchName) BranchGroup
FROM cteFinal
) F
WHERE F.BranchGroup = 1 OR F.DescriptionGroup = 1
--ORDER BY F.BranchName, F.InvoiceLineDescription
END

-- Note that providing data like this in your question speeds up the answer
DECLARE 
@T TABLE (ID INT, CompanyName VARCHAR(100), MembershipType VARCHAR(100), BranchName VARCHAR(100), Description VARCHAR(100))
INSERT INTO @T VALUES(20,'DeluxPainting','BuyerMemberShip','Branch1','Branch MonthlyMembership')
INSERT INTO @T VALUES(20,'DeluxPainting','BuyerMemberShip','Branch1','Buyer Monthly Membership')
INSERT INTO @T VALUES(20,'DeluxPainting','BuyerMemberShip','Branch1','Carrying Charge')
INSERT INTO @T VALUES(20,'DeluxPainting','BuyerMemberShip','Branch2','Branch MonthlyMembership')
INSERT INTO @T VALUES(20,'DeluxPainting','BuyerMemberShip','Branch2','Buyer Monthly Membership')
INSERT INTO @T VALUES(20,'DeluxPainting','BuyerMemberShip','Branch2','Carrying Charge')
INSERT INTO @T VALUES(20,'DeluxPainting','BuyerMemberShip','Branch3','Branch MonthlyMembership')
INSERT INTO @T VALUES(20,'DeluxPainting','BuyerMemberShip','Branch3','Buyer Monthly Membership')
INSERT INTO @T VALUES(20,'DeluxPainting','BuyerMemberShip','Branch3','Carrying Charge')
-- A. Original data with magic numbers added based on partition functions
-- THIS IS FOR ILLUSTRATION ONLY. It is not needed for the final answer
SELECT * , 
ROW_NUMBER() OVER (PARTITION BY BranchName ORDER BY BranchName, Description ) DescriptionGroup,
ROW_NUMBER() OVER (PARTITION BY Description ORDER BY BranchName) BranchGroup
FROM @T
-- B. Once you have the magic numbers worked out, just filter out the rows you don't want
SELECT * FROM (
SELECT * , 
ROW_NUMBER() OVER (PARTITION BY BranchName ORDER BY BranchName, Description ) DescriptionGroup,
ROW_NUMBER() OVER (PARTITION BY Description ORDER BY BranchName) BranchGroup
FROM @T
) F
WHERE F.BranchGroup = 1 OR F.DescriptionGroup = 1
ORDER BY F.BranchName, F.Description