Help with an SQL select query
Hi all, I have a table called products (a dummy data version is in the code box below) and I want to change the output data (not the data stored just what comes out from the query) in the column "ProductClass" to one of the following below: (dependent on what is already in that field)
Hats
Bags
Other
Example of the Table:
ID NAME PRODUCTCLASS
1 foo 123
2 som 456
3 gee 789
4 hhh xyz
5 sss asd
6 ppp abc
7 bbb bar
The result should then be (in the output from the Select not updating the DB)
ID NAME PRODUCTCLASS
1 foo Hats
2 som Hats
3 gee Hats
4 hhh Bags
5 sss Bags
6 ppp Bags
7 bbb Other
A dummy if statement is in the code field.
Any help would be greatly appreciated.
Xavier.
Hats
Bags
Other
Example of the Table:
ID NAME PRODUCTCLASS
1 foo 123
2 som 456
3 gee 789
4 hhh xyz
5 sss asd
6 ppp abc
7 bbb bar
The result should then be (in the output from the Select not updating the DB)
ID NAME PRODUCTCLASS
1 foo Hats
2 som Hats
3 gee Hats
4 hhh Bags
5 sss Bags
6 ppp Bags
7 bbb Other
A dummy if statement is in the code field.
Any help would be greatly appreciated.
Xavier.
if(ProductClass = "abc" or ProductClass = "asd" or ProductClass = "xyz") then
{
ProductClass = "Bags";
}
else
{
if(ProductClass = "123" or ProductClass = "456" or ProductClass = "789") then ProductClass = "Hats";
}
else
{
ProductClass = "Other";
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you can do something like
SELECT ID,NAME,
ProductClass=CASE WHEN ProductClass='abc' or ProductClass='asd' or ProductClass='xyz' then 'bags' end
from productSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
;) bit late in answering!!!
ASKER
Is there a way to use * rather than specifying the fields? Nb. the query below uses actual objects and field names:
Select TOP 100 *
CASE
WHEN PRODUCTCLASS in ('HATS','IHAT') THEN 'HATS'
WHEN PRODUCTCLASS in ('JACK','IJAC') THEN 'JACK'
ELSE 'Other Shit' END
from dbo.ArTrnDetail
Select TOP 100 *
CASE
WHEN PRODUCTCLASS in ('HATS','IHAT') THEN 'HATS'
WHEN PRODUCTCLASS in ('JACK','IJAC') THEN 'JACK'
ELSE 'Other Shit' END
from dbo.ArTrnDetail
ASKER
scratch that missing a comma thanks
you can do like this.
SELECT top 100 *,
ProductClass=CASE
WHEN ProductClass='abc' or ProductClass='asd' or ProductClass='xyz' then 'bags'
WHEN ProductClass='123' or ProductClass='456' or ProductClass='789' then 'hats'
else 'Other'
end
from product
SELECT top 100 *,
ProductClass=CASE
WHEN ProductClass='abc' or ProductClass='asd' or ProductClass='xyz' then 'bags'
WHEN ProductClass='123' or ProductClass='456' or ProductClass='789' then 'hats'
else 'Other'
end
from product
SELECT ID, NAME,
CASE WHEN PRODUCTCLASS IN ('123', '456', '789') THEN 'Hats'
WHEN PRODUCTCLASS IN ('xyz', 'asd','abc') THEN 'Bags'
WHEN PRODUCTCLASS IN ('bar') THEN 'Other'
END AS PRODUCTCLASS
FROM products