Create Table dbo.Clients
(Client_ID Int Identity(1,1) Primary Key
 ,ClientName Varchar(100))
Go

--------------------
Create Table dbo.Products
(Product_ID Int Identity(1,1) Primary Key
 ,ProductName Varchar(100))
Go

--------------------
Insert Into dbo.Clients
(ClientName)
Select 'Client 1'
  Union All
Select 'Client 2'
Go

--------------------
Insert Into dbo.Products
(ProductName)
Select 'Product 1'
  Union All
Select 'Product 2'
  Union All
Select 'Product 3'
Go

Create Table Client_Products_Asoc
(Client_Products_Asoc_ID Int Identity(1,1) Primary Key
 ,Client_ID Int References dbo.Clients(Client_ID)
 ,Product_ID Int References dbo.Products(Product_ID))
Go

--------------------
Insert Into dbo.Client_Products_Asoc
(Client_ID
 ,Product_ID)
Select 1,1
  Union All
Select 1,3
  Union All
Select 2,1
  Union All
Select 2,2
Go

Create Table Client_Products_Asoc
(Client_Products_Asoc_ID Int Identity(1,1) Primary Key
 ,Client_ID Int References dbo.Clients(Client_ID)
 ,Product_ID Int References dbo.Products(Product_ID))
Go

Select C.Name As ClientName
  ,P.Name As ProductName
  ,Sum(P.Sales) As TotalSales
From dbo.Client C Inner Join dbo.ClientProduct_Asoc CPA
    On C.Client_ID = CPA.Client_ID
  Inner Join dbo.Product P
    On P.Product_ID = CPA.Product_ID
Where CPA.Inactive <> 'Y'

Create Table Client_Products_Asoc(Client_Products_Asoc_ID Int Identity(1,1) Primary Key ,Client_ID Int References dbo.Clients(Client_ID) ,Product_ID Int References dbo.Products(Product_ID))Go

	--this table identifies the clients
	Create Table Clients(
	Client_ID Int Identity(1,1) Primary Key ,
	ClientName Varchar(100))
	Go
	
	Insert Into Clients(ClientName)
	Select 'Client 1'  
	Union All
	Select 'Client 2'
	Go
	--------------------
	--this table identifies the products, i'm assuming there will be more columns
	Create Table Products(
	Product_ID Int Identity(1,1) Primary Key ,
	ProductName Varchar(100),
	ProductCost Decimal(28,4))
	Go
	
	Insert Into Products(ProductName, ProductCost)
	Select 'Product 1' , 49.95 
	Union All
	Select 'Product 2' , 15
	Union All
	Select 'Product 3' , 777.77
	Go
	--------------------
	--this table associates the clients and products and provides a place for custom name
	Create Table Client_Products(	
	Client_ID Int References Clients(Client_ID) not null,
	Product_ID Int References Products(Product_ID) not null,
	ClientProductName varchar(100) null,
	PRIMARY KEY (Client_ID, Product_ID)
	)
	Go
	
	Insert Into Client_Products (Client_ID , Product_ID , ClientProductName )
	select 1,1,null
	union all
	select 1,2, 'Beer Popsicle'
	union all
	select 2,2, null
	union all
	select 2,3, 'Our Thingy'
	union all
	select 1,3, null
	Go
	--------------------
	--if you want to see which products a client is associated with
	select
		c.client_ID
		,c.ClientName 
		,p.Product_ID 
		,coalesce(cp.ClientProductName, p.ProductName)
	
	from 
		Client_Products cp
		inner join Clients c on c.Client_ID = cp.Client_ID 
		inner join Products p on p.Product_ID = cp.Product_ID 
	;
	--------------------	
	--you said you have a transaction table, this is a dummy version without your ddl
	--just to show how to pull out data
	
	Create Table ClientTransactions(Client_ID Int, Product_ID Int, Quantity Int)
	go
	Insert Into ClientTransactions 
	select 1,1,40 union all
	select 1,2,200 union all
	select 1,3,10 union all
	select 1,1,16 union all
	select 2,1,1 union all
	select 2,2,50 union all
	select 2,3,40 union all
	select 1,2,6 union all
	select 2,3,4
	go
	
	select
		c.client_ID
		,c.ClientName 
		,p.Product_ID 
		,coalesce(cp.ClientProductName, p.ProductName)
		,TotalQuantity=SUM(t.Quantity)
		,TotalPrice=SUM(t.Quantity * p.ProductCost)
	
	from 
		ClientTransactions t		
		inner join Clients c on c.Client_ID = t.Client_ID 
		inner join Products p on p.Product_ID = t.Product_ID 
		inner join Client_Products cp on cp.Client_ID = t.Client_ID and cp.Product_ID = t.Product_ID 
		
	
	group by 
		c.client_ID
		,c.ClientName 
		,p.Product_ID 
		,coalesce(cp.ClientProductName, p.ProductName)
	;