July 8, 2015 at 9:29 am
I have a table with items, sales locations, and product groups. The product group can be different for each location for the same item. I need to find each item where the product group is different between locations. I have 7 locations (sample below has 3) and a whole bunch of product groups. I’m thinking a pivot. Here is sample data. I would want to return all columns where the locations were different product groups. In the case of the last insert, item TV has the same product group at all locations, so I don’t need to see it.
CREATE TABLE data1 (item_id varchar(40), location_id decimal(10), product_group_id varchar(8))
INSERT INTO data1
VALUES ('AB', 1, 'PG1'),
('AB', 40, 'PG2'),
('AB', 300, 'PG2'),
('XY', 1, 'PG5'),
('XY', 40, 'PG9'),
('XY', 300, 'PG5'),
('QR', 1, 'PG3'),
('QR', 40, 'PG4'),
('QR', 300, 'PG5'),
('TV', 1, 'PG7'),
('TV', 40, 'PG7'),
('TV', 300,'PG7')
I'm close with this (actual tables from my db), but it's showing all items even if the product groups match across all locations. I really just want to see the items that have different groups.
SELECT item_id, [1], [40], [300]
FROM
(SELECT item_id
, location_id
, product_group_id
FROM inv_mast m
INNER JOIN inv_loc l
ON l.inv_mast_uid = m.inv_mast_uid
WHERE m.delete_flag = 'N') AS X
PIVOT (MAX(product_group_id) FOR location_id IN ([1], [40], [300])) AS T
July 8, 2015 at 10:25 am
jcobb 20350 (7/8/2015)
I have a table with items, sales locations, and product groups. The product group can be different for each location for the same item. I need to find each item where the product group is different between locations. I have 7 locations (sample below has 3) and a whole bunch of product groups. I’m thinking a pivot. Here is sample data. I would want to return all columns where the locations were different product groups. In the case of the last insert, item TV has the same product group at all locations, so I don’t need to see it.CREATE TABLE data1 (item_id varchar(40), location_id decimal(10), product_group_id varchar(8))
INSERT INTO data1
VALUES ('AB', 1, 'PG1'),
('AB', 40, 'PG2'),
('AB', 300, 'PG2'),
('XY', 1, 'PG5'),
('XY', 40, 'PG9'),
('XY', 300, 'PG5'),
('QR', 1, 'PG3'),
('QR', 40, 'PG4'),
('QR', 300, 'PG5'),
('TV', 1, 'PG7'),
('TV', 40, 'PG7'),
('TV', 300,'PG7')
I'm close with this (actual tables from my db), but it's showing all items even if the product groups match across all locations. I really just want to see the items that have different groups.
SELECT item_id, [1], [40], [300]
FROM
(SELECT item_id
, location_id
, product_group_id
FROM inv_mast m
INNER JOIN inv_loc l
ON l.inv_mast_uid = m.inv_mast_uid
WHERE m.delete_flag = 'N') AS X
PIVOT (MAX(product_group_id) FOR location_id IN ([1], [40], [300])) AS T
I'm a visually oriented type. Based on the sample data you provided, what is your expected output?
July 8, 2015 at 10:37 am
I'm not sure which of the 2 HAVING clauses you need so I'm including both so you can test.
I also changed the pivot approach to a cross tabs which is more flexible and might improve performance in some cases.
SELECT item_id
, MAX(CASE WHEN location_id = 1 THEN product_group_id END) AS [1]
, MAX(CASE WHEN location_id = 40 THEN product_group_id END) AS [40]
, MAX(CASE WHEN location_id = 300 THEN product_group_id END) AS [300]
FROM Data1
GROUP BY item_id
HAVING COUNT( DISTINCT product_group_id) = 3
--HAVING COUNT( DISTINCT product_group_id) > 1
July 8, 2015 at 11:25 am
Lynn Pettis (7/8/2015)
jcobb 20350 (7/8/2015)
I have a table with items, sales locations, and product groups. The product group can be different for each location for the same item. I need to find each item where the product group is different between locations. I have 7 locations (sample below has 3) and a whole bunch of product groups. I’m thinking a pivot. Here is sample data. I would want to return all columns where the locations were different product groups. In the case of the last insert, item TV has the same product group at all locations, so I don’t need to see it.CREATE TABLE data1 (item_id varchar(40), location_id decimal(10), product_group_id varchar(8))
INSERT INTO data1
VALUES ('AB', 1, 'PG1'),
('AB', 40, 'PG2'),
('AB', 300, 'PG2'),
('XY', 1, 'PG5'),
('XY', 40, 'PG9'),
('XY', 300, 'PG5'),
('QR', 1, 'PG3'),
('QR', 40, 'PG4'),
('QR', 300, 'PG5'),
('TV', 1, 'PG7'),
('TV', 40, 'PG7'),
('TV', 300,'PG7')
I'm close with this (actual tables from my db), but it's showing all items even if the product groups match across all locations. I really just want to see the items that have different groups.
SELECT item_id, [1], [40], [300]
FROM
(SELECT item_id
, location_id
, product_group_id
FROM inv_mast m
INNER JOIN inv_loc l
ON l.inv_mast_uid = m.inv_mast_uid
WHERE m.delete_flag = 'N') AS X
PIVOT (MAX(product_group_id) FOR location_id IN ([1], [40], [300])) AS T
I'm a visually oriented type. Based on the sample data you provided, what is your expected output?
I'd like to see the items where the PG is different in at least two locations. So I would see items AB, XY, and QR along with their PGs under their location as a column name. I would not see TV because the PG is the same in all locations.
July 8, 2015 at 11:33 am
Luis Cazares (7/8/2015)
I'm not sure which of the 2 HAVING clauses you need so I'm including both so you can test.I also changed the pivot approach to a cross tabs which is more flexible and might improve performance in some cases.
SELECT item_id
, MAX(CASE WHEN location_id = 1 THEN product_group_id END) AS [1]
, MAX(CASE WHEN location_id = 40 THEN product_group_id END) AS [40]
, MAX(CASE WHEN location_id = 300 THEN product_group_id END) AS [300]
FROM Data1
GROUP BY item_id
HAVING COUNT( DISTINCT product_group_id) = 3
--HAVING COUNT( DISTINCT product_group_id) > 1
This works by using the second HAVING, where the count is greater than 1. Thanks so much. Side question: How do you post code so that it appears in the blue box, all nice and neat?
July 8, 2015 at 11:37 am
jcobb 20350 (7/8/2015)
Luis Cazares (7/8/2015)
I'm not sure which of the 2 HAVING clauses you need so I'm including both so you can test.I also changed the pivot approach to a cross tabs which is more flexible and might improve performance in some cases.
SELECT item_id
, MAX(CASE WHEN location_id = 1 THEN product_group_id END) AS [1]
, MAX(CASE WHEN location_id = 40 THEN product_group_id END) AS [40]
, MAX(CASE WHEN location_id = 300 THEN product_group_id END) AS [300]
FROM Data1
GROUP BY item_id
HAVING COUNT( DISTINCT product_group_id) = 3
--HAVING COUNT( DISTINCT product_group_id) > 1
This works by using the second HAVING, where the count is greater than 1. Thanks so much. Side question: How do you post code so that it appears in the blue box, all nice and neat?
Select the code. Then, on the left side, under the "IFCode Shortcuts", select the one for "[ code="sql" ] [ / code ]". (Without all the spaces, which throws off the reply.)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 8, 2015 at 11:39 am
I use the IFCode tags which are visible to the left of the message text box. For SQL code, I use the [ code="sql"][/code] with no space. You can also check how I used them by quoting the post.
Do you have any questions about the code?
July 8, 2015 at 11:44 am
Fantastic. Know I know how to post better code when I need help again. Thanks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply