Jump to content
Sign in to follow this  
bpajank

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,

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!

Share this post


Link to post
Share on other sites
Quote

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×