question

Here's a quick example that shows the result you want, but I'm guessing there's more to the 'real' table than this, so it might not perform against your live data, but might get you thinking along the right lines. This query uses self joins to query against itself for the specific subsets of data declare @YourTable table ( ID int, B varchar(10), C varchar(10), D varchar(10) ) insert into @YourTable select 1,null,null,null insert into @YourTable select 2,null,null,null insert into @YourTable select 3,null,null,null insert into @YourTable select 4,'*1',null,null insert into @YourTable select 5,null,null,null insert into @YourTable select 6,'**1',null,null insert into @YourTable select 7,null,null,null insert into @YourTable select 8,null,null,null insert into @YourTable select 9,'**2',null,null insert into @YourTable select 10,'*2',null,null select * from @YourTable select t1.ID, t1.B, min(t2.B) as C, min(t3.B) as D from @YourTable t1 left join @YourTable t2 on t1.ID <= t2.ID and t2.B like '*[0-9]' left join @YourTable t3 on t1.ID <= t3.ID and t3.B like '**[0-9]' group by t1.ID, t1.B order by t1.ID and gives this result ID B C D ----------- ---------- ---------- ---------- 1 NULL *1 **1 2 NULL *1 **1 3 NULL *1 **1 4 *1 *1 **1 5 NULL *2 **1 6 **1 *2 **1 7 NULL *2 **2 8 NULL *2 **2 9 **2 *2 **2 10 *2 *2 NULL