bpajank

I'm pretty new to SQL and I'm working on a query that will save me so much time at work. 

I have three tables-

Product- Contains every product (product info table)

Company Offering- Contains every company the product is offered to. (For example, a pen from the product file is listed 10 times on the company offering file because it is available to Company 1, 2, 3, etc.

Companies- Table contains every company ID and company

I need to know which COMPANIES the item is not available to and vice versa (i.e. If someone said start/stop selling this product, I need to know which companies to add/remove it from)

Here is my query: 

select s.usn,

s.web_short_description,

s.manufacturer_part_number,

bs.brand_id, bs.source_id,

bs.source_system_id,

bs.brand_SKU_number,

bs.web_enable_date,

bs.web_disable_date,

BR.brand_name

FROM PRODUCT S WITH (NOLOCK)

left join COMPANYOFFERING BS WITH (NOLOCK) on s.usn=bs.USN

join COMPANIES BR WITH (NOLOCK) ON BS.brand_id = BR.Brand_ID

where s.usn is not null

This pulls in all of the data i need, but it's not summarized- each product has several rows- 1 for each company. Ideally, all company names would be listed as a column, and if the item didn't exist for that company, I would get a NULL result. 

Thanks in advance for the help!