Jump to content

Pivot and Summarizing Text Rows

Recommended Posts

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,



bs.brand_id, bs.source_id,









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!

Link to post
Share on other sites

Ideally, all company names would be listed as a column

SQL doesn't work that way, each column has a single value.  What you can do is do a query that shows you how many companies sell each product, and another query that shows which products are not sold by any company.  You can do another one to list every company that sells a particular product.

For one of them:

SELECT p.usn, p.web_short_description, COUNT(*) AS num_sellers FROM products AS p INNER JOIN companyoffering AS o ON p.usn=o.usn GROUP BY p.usn, p.web_short_description

This should list products not sold by anyone:

SELECT * FROM products WHERE usn NOT IN (SELECT usn FROM companyofferings)

This will do the same thing and may be faster depending on how many records are in the tables:

SELECT * FROM products AS p WHERE NOT EXISTS (SELECT * FROM companyofferings AS o WHERE p.usn=o.usn)

And then to list companies:

SELECT * FROM companies AS c WHERE EXISTS (SELECT * FROM companyofferings AS o WHERE o.brand_id=c.brand_id AND o.usn=123)

You'll need to enter the USN you want to figure out which companies sell in that query.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...