I have the below query and am unable to get a result from it, the area is 8 digit range am trying to have the quantity in area start with range 1P[01-99]Kxxx

select 
    label 
    , coalesce (sum(case when left(area,5) ='1P__K' then quantity end), 0) as '1P_K'
from my_table with (nolock) 
group by label

Sample Table :

Expected Result :

Looking forwards to your kind help

CodePudding user response：

You just need to change = to like as equals requires an exact match whereas you want a similar match.

case when left(area,5) like '1P__K' then quantity else 0 end

Or you could continue to use wildcards and remove left e.g.

case when area like '1P__K%' then quantity else 0 end

Works as follows:

declare @Test table ([Label] varchar(2), Area varchar(12), QTY int);

insert into @Test ([Label], Area, QTY)
values
('L1', '1p01k458', 10),
('L1', '1p58K874', 20),
('L1', '1p96k786', 30),
('L1', '1p99k785', 40),
('L1', '1q99k785', 30), -- row that doesn't match the criteria
('L2', '1p58K874', 22), -- row for different area
('L2', '1p96k786', 35);

select 
    [label]
    -- Solution 1 
    , sum(case when left(Area,5) like '1P__K' then QTY else 0 end) as '1P_K'
    -- Solution 2 
    , sum(case when Area like '1P__K%' then QTY else 0 end) as '1P_K'
from @Test 
group by label;

Returning

Note: adding the sample data as DDL DML (as I have) makes it much easier to answer. Also I've added an additional row which should be excluded by your matching logic - because its important to test such cases. And added another area to prove that that also works.