hello Oracle Team I have this test query right now and I have this table.
id | cost_center |hardware
beni | aaaa |PC123
beni | aaaa |PC234
ari | bbbb |PC456
cari | cccc |PC678
I need to create indicate in the hardware column if a user has multiple machines. If a user has more than 1 hard ware, the hardware column or a new column will be written as 'Multiple HW'
If a user has one machines only, the hardware or the new column will be written with the hardware data for that user.
sample output:
id | cost_center |hardware
beni | aaaa |Multiple HW
ari | bbbb |PC456
cari | cccc |PC678
I have this query.
Select distinct id,cost_center,
case
when count(hardware) > 1
then 'Multiple Hardware'
when count(hardware) = 1
then Hardware
end as hardware_new
from table
please help me. thank you very much.
this query is only applicable if I set the then statement into a string, but I need to call it out from the column.
im new to this I tried searching but I cannot find the correct statement perhaps.
What you're looking for is group by, not distinct:
create table t (
id varchar2(10), cost_center varchar2(10), hardware varchar2(10)
);
insert into t values ('beni','aaaa','PC123');
insert into t values ('beni','aaaa','PC234');
insert into t values ('ari','bbbb','PC456');
insert into t values ('cari','cccc','PC678');
Select id,cost_center,
case
when count(distinct hardware) > 1
then 'Multiple Hardware'
when count(distinct hardware) = 1
then min(Hardware)
end as hardware_new
from t
group by id,cost_center;
ID COST_CENTER HARDWARE_NEW
cari cccc PC678
beni aaaa Multiple Hardware
ari bbbb PC456