May 14, 2012 at 3:36 am
Hiya,
Is there a way to handle multiple different values, but treat them as the same.
I.e;
NO | NAME
-----------------------
1 | Samsung Corporation
2 | Samsung Electro-Mechanics Co., Ltd.
3 | Samsung NEC Mobile Display Co., Ltd
So rather than doing;
CASE WHEN NAME LIKE 'Samsung%' THEN 'Samsung' ELSE NAME END
and then repeat for all names I want to do the same thing for (which could be hundrends in my case), can I do something more elegant that basically detects if the start is the same, and if so give them all the same shared name?
So a query would result in;
COUNT | Name
----------------------
3 | Samsung
Thanks
May 14, 2012 at 3:40 am
Case when name LIKE '%samsung%' then 'Samsung'
or are you trying to assign 3 different case conditions like:
Case when condition1 then result1 when condition2 then result2 when condition3
then result3 else end
?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 14, 2012 at 5:00 am
Are you looking something like this?
declare @tblMain table (NO int, NAME varchar(100))
insert into @tblMain (NO, NAME) values
(1, 'Samsung Corporation'),
(2, 'Samsung Electro-Mechanics Co., Ltd.'),
(3, 'Samsung NEC Mobile Display Co., Ltd'),
(4, 'LG Corp'),
(5, 'LG Global')
Declare @tblFilters table (Type varchar(25))
Insert into @tblFilters (Type) Values ('Samsung'), ('LG')
Select
Count(Main.NO) as [Count],
Filters.Type as [Name]
From
@tblMain Main
Inner Join
@tblFilters Filters
On
Main.NAME like Filters.Type + '%'
Group By
Filters.Type
May 14, 2012 at 5:17 am
lanky_doodle (5/14/2012)
Hiya,Is there a way to handle multiple different values, but treat them as the same.
I.e;
NO | NAME
-----------------------
1 | Samsung Corporation
2 | Samsung Electro-Mechanics Co., Ltd.
3 | Samsung NEC Mobile Display Co., Ltd
So rather than doing;
CASE WHEN NAME LIKE 'Samsung%' THEN 'Samsung' ELSE NAME END
and then repeat for all names I want to do the same thing for (which could be hundrends in my case), can I do something more elegant that basically detects if the start is the same, and if so give them all the same shared name?
So a query would result in;
COUNT | Name
----------------------
3 | Samsung
Thanks
Right now, I can think of something like this, hope this helps:
--=================================== Preparing test data
DECLARE @Products TABLE
(
ID INT IDENTITY,
Product NVARCHAR(200)
)
INSERT INTO @Products VALUES ('LG Television')
INSERT INTO @Products VALUES ('LG Mobile')
INSERT INTO @Products VALUES ('LG Washing Machine')
INSERT INTO @Products VALUES ('Samsung Television')
INSERT INTO @Products VALUES ('Samsung Mobile')
INSERT INTO @Products VALUES ('Samsung Refrigerator')
INSERT INTO @Products VALUES ('Samsung AirConditioner')
--========================= Calculating product count here
;WITH MyProductCTE (ID,Product)
AS
(
SELECT ID,CASE
WHEN Product LIKE SUBSTRING(Product,1,CHARINDEX(' ',Product)-1)+'%'
THEN SUBSTRING(Product,1,CHARINDEX(' ',Product)-1)
ELSE Product
END AS Product
FROM @Products
)
SELECT Product, COUNT(Product) AS ProductCount FROM MyProductCTE GROUP BY Product
May 14, 2012 at 7:35 am
Thanks all.
Divine Flame, yours seems the best to follow, but I get this;
Msg 537, Level 16, State 3, Line 9
Invalid length parameter passed to the LEFT or SUBSTRING function.
Not all my names will have spaces, does that have something to do with the error?
EDIT: Definately the rows without spaces causing this as if I add a clause to WHERE to exclude those with spaces in, all works fine...
EDIT 2: Actually, it's not quite right. It's obviously doing it for all rows where there is a space - can it be done so it only applies to rows where the first word is the same amongst many rows?
May 14, 2012 at 8:17 am
I think the best way to do this is code it into your table with a foreign key to the overarching corporation. That way you can prevent things like grouping Johnson & Johnson and Johnsonville Meats or Smith Brothers and Smith & Wesson.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 14, 2012 at 8:20 am
lanky_doodle (5/14/2012)
Thanks all.Divine Flame, yours seems the best to follow, but I get this;
Msg 537, Level 16, State 3, Line 9
Invalid length parameter passed to the LEFT or SUBSTRING function.
Not all my names will have spaces, does that have something to do with the error?
EDIT: Definately the rows without spaces causing this as if I add a clause to WHERE to exclude those with spaces in, all works fine...
EDIT 2: Actually, it's not quite right. It's obviously doing it for all rows where there is a space - can it be done so it only applies to rows where the first word is the same amongst many rows?
Yes you are right , it was the problem caused by those rows where it does not have any spaces. Below query should work fine for all the rows:
--========================= Calculating product count here
;WITH MyProductCTE (ID,Product)
AS
(
SELECT ID,CASE
WHEN CHARINDEX(' ',Product) > 0
THEN SUBSTRING(Product,1,CHARINDEX(' ',Product)-1)
ELSE Product
END AS Product
FROM @Products
)
SELECT Product, COUNT(Product) AS ProductCount FROM MyProductCTE GROUP BY Product
May 14, 2012 at 8:27 am
drew.allen (5/14/2012)
I think the best way to do this is code it into your table with a foreign key to the overarching corporation. That way you can prevent things like grouping Johnson & Johnson and Johnsonville Meats or Smith Brothers and Smith & Wesson.Drew
Yeah, it could be even worse:
"Apple" and "Apple Ciders Co"
or
"Coca Cola" and "Coca and Cocaine Production Unlimited"
:hehe:
You do better follow d.a. suggestion...
May 14, 2012 at 8:42 am
drew.allen (5/14/2012)
I think the best way to do this is code it into your table with a foreign key to the overarching corporation. That way you can prevent things like grouping Johnson & Johnson and Johnsonville Meats or Smith Brothers and Smith & Wesson.Drew
I would love to be able to do this but unfortunately it's a 3rd party DB, so have no 'access' to do such modifications.
Ideally each company who has sub-divisions would have a parent company name, but oh no this is a proper piece of junk software (the front end client can't use scroll wheels found on mice since time began and the cursor is the wrong way round!!!)
May 14, 2012 at 8:59 am
You can still create a temporary table or table variable that holds this information. It probably isn't as good a solution as incorporating it directly into the database, but it will make it relatively easy to maintain and give you more accurate results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply