SQL Server : pick value from earlier row based on condition-CodePudding

I have a table with 3 columns OrderNo, CategoryType and CategoryName. CategoryType tells us if the category in CategoryName is the main category or a sub-category. It is set to blank for main categories and is set to Sub Category for sub-categories.

I need to create a 4th column Main_Category_Name which is populated for each row with the name of the main category which is "right above" the sub-category. "Right above" is decided based on OrderNo.

The code I have currently written uses the LAG function and populates the category name from the previous row - which is not quite what I want.

I've included my code, and the current vs desired output below. Can someone please help. Thanks.

drop table if exists temp

create table temp
( 
    OrderNo int, 
    CategoryType varchar(100), 
    CategoryName varchar(100)
)

insert into temp values (1, '', 'Cat1')
insert into temp values (2, '', 'Cat2')
insert into temp values (3, 'Sub Category', 'SubCat1')
insert into temp values (4, 'Sub Category', 'SubCat2')
insert into temp values (5, '', 'Cat3')
insert into temp values (6, 'Sub Category', 'SubCat1')
insert into temp values (7, 'Sub Category', 'SubCat2')
insert into temp values (8, 'Sub Category', 'SubCat3')
insert into temp values (9, 'Sub Category', 'SubCat4')
insert into temp values (10, '', 'Cat4')


select 
    *, 
    case 
        when CategoryType = 'Sub Category' 
            then lag(CategoryName, 1, 0) over (order by OrderNo) 
            else '' 
    end as Main_Category_Name
from temp

CodePudding user response：

One solution is to use a typical outer apply top 1 pattern.

Side note: insert ... values can do multiple rows at once! Separate each row with a comma. Example included:


create table #temp
(
   OrderNo int, 
   CategoryType varchar(100), 
   CategoryName varchar(100)
);

insert #temp values 
(1, '', 'Cat1'),
(2, '', 'Cat2'),
(3, 'Sub Category', 'SubCat1'),
(4, 'Sub Category', 'SubCat2'),
(5, '', 'Cat3'),
(6, 'Sub Category', 'SubCat1'),
(7, 'Sub Category', 'SubCat2'),
(8, 'Sub Category', 'SubCat3'),
(9, 'Sub Category', 'SubCat4'),
(10, '', 'Cat4');


select      t.orderNo,
            t.categoryType,
            t.categoryName,
            Main_Category_Name = p.CategoryName
from        #temp    t
outer apply (
               select   top 1 CategoryName
               from     #temp
               where    OrderNo < t.OrderNo
                        and CategoryType = ''
                        and t.CategoryType = 'Sub Category'
               order by OrderNo desc
            ) p;