clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2249064 fiddles created (32143 in the last week).

CREATE TABLE Components ( ABC varchar(30), [Level] int ); INSERT INTO Components(ABC, [Level]) VALUES('1234Z2DD', 0), ('1234Z2DD',1), ('1234Z2DD',2), ('1234Z2DD',2), ('1234Z2DD',3), ('1234Z2DD',3), ('1234Z2DD',3), ('1234Z2DD',3), ('1234Z2DD',2), ('1234Z2DD',3), ('1234Z2DD',2), ('1234Z2DD',3), ('1234Z2DD',2), ('1234Z2DD',3), ('1234Z2DD',2), ('1234Z2DD',3), ('1234Z2DD',2), ('1234Z2DD',3), ('1234Z2DD',1), ('1234Z2DD',2), ('1234Z2DD',3), ('1234Z2DD',2), ('1234Z2DD',3), ('1234Z2DD',2), ('1234Z2DD',2), ('1234Z2DD',3), ('1234Z2DD',1), ('1234Z2DD',1), ('1234Z2DD',2);
29 rows affected
 hidden batch(es)


SELECT ABC, [Level], Avail = CASE WHEN [Level] = 0 THEN 'NA' WHEN [Level] = 1 AND LEAD([Level]) OVER(ORDER BY ABC) > 1 THEN 'Available' ELSE '' END FROM COmponents;
ABC Level Avail
1234Z2DD 0 NA
1234Z2DD 1 Available
1234Z2DD 2
1234Z2DD 2
1234Z2DD 3
1234Z2DD 3
1234Z2DD 3
1234Z2DD 3
1234Z2DD 2
1234Z2DD 3
1234Z2DD 2
1234Z2DD 3
1234Z2DD 2
1234Z2DD 3
1234Z2DD 2
1234Z2DD 3
1234Z2DD 2
1234Z2DD 3
1234Z2DD 1 Available
1234Z2DD 2
1234Z2DD 3
1234Z2DD 2
1234Z2DD 3
1234Z2DD 2
1234Z2DD 2
1234Z2DD 3
1234Z2DD 1
1234Z2DD 1 Available
1234Z2DD 2
 hidden batch(es)