How to create new column group by Category instead of Line Item

Transact-SQL
1

Hi, i have a list of same category but different sub line item. I would like to categories and create new column with case condition. For example below, case when amount found 0.00 i would like to create additional column and apply all as "ZeroFound".

create table #testing
(
category varchar(100),
amount float,
lineitem varchar(10)
)

insert into #testing values('typeA','0.00','1A')
insert into #testing values('typeA','20.99','1B')
insert into #testing values('typeA','0.80','1C')
insert into #testing values('typeA','1000','1D')

select * from #testing

2 
create table #testing
(
category varchar(100),
amount float,
lineitem varchar(10)
)

insert into #testing values('typeA','0.00','1A')
insert into #testing values('typeA','20.99','1B')
insert into #testing values('typeA','0.80','1C')
insert into #testing values('typeA','1000','1D')

select *, iif(amount = 0, 'ZeroFound', 'Found Some')
  from #testing
  
drop table #testing

let's see what @JeffModen says about 1million rows performance :wink:

3

I don't really see a practical purpose to this unless it's an exercise from a study course or, perhaps, a test for such a course or an interview question.

4

Hi, I tested this result. The output should be if zero found it will apply for all rows in the same Category.

The purpose of this it to patch and group them into same item for courier.