Generate new line number for each mapping

  • A+
Category:Languages

I need to create a new line number of each unit in my mapping table. Please check the below sample data and expected result.

Lines table +--------+------------+------+------+ | FileId | linenumber | code | unit | +--------+------------+------+------+ |      1 |          1 | A    | NULL | |      1 |          2 | B    | NULL | |      1 |          3 | C    | NULL | +--------+------------+------+------+  map table +------+------+ | code | unit | +------+------+ | A    | c1   | | A    | c2   | | B    | c3   | | B    | c4   | | B    | c5   | +------+------+  expected result +--------+------------+------+------+ | FileId | Linenumber | code | unit | +--------+------------+------+------+ |      1 |          1 | A    | c1   | |      1 |          2 | B    | c3   | |      1 |          4 | A    | c2   | |      1 |          5 | B    | c4   | |      1 |          6 | B    | c5   | +--------+------------+------+------+ 

Code A has two units (c1 and c2), unit c1 will be updated in line number 1 and c2 unit should be inserted as new line with line number after the last available linenumber in lines table. Same process should happen for all the codes

My current approach

if object_id('tempdb..#lines') is not null drop table #lines if object_id('tempdb..#map') is not null drop table #map if object_id('tempdb..#Files') is not null drop table #Files if object_id('tempdb..#Maptemp') is not null drop table #Maptemp  create table #lines(FileId int, linenumber int, code varchar(10), unit varchar(10)) create table #map(code varchar(10), unit varchar(10)) insert into #lines values (1,1,'A',null), (1,2,'B',null),(1, 3,'C',null) insert into #map values ('A','c1'),('A','c2'),('B','c3'),('B','c4'),('B','c5')  select FileId, MaxLinenum = max(linenumber) into #Files     from #lines     group by FileId  select row_number()over(partition by code order by (select null)) Rn,* into #Maptemp     from #map  select l.FileId,l.Linenumber,l.code, m.unit     from #lines l      inner join #Files f on l.FileId = f.FileId     inner join #Maptemp m on m.code = l.code      where m.rn = 1 union all select l.FileId, f.MaxLinenum +row_number()over(partition by f.FileId order by (select null)),l.code, m.unit     from #lines l      inner join #Files f on l.FileId = f.FileId     inner join #Maptemp m on m.code = l.code      where m.rn > 1 

It works fine, but I feel I have done little too much of coding for this. So is there a better way to achieve this ?

 


This is my try.. You may need some changes according your actual schema.

DECLARE @MAXLINE INT = (SELECT MAX(linenumber) FROM #lines)  SELECT L.FileId     ,CASE WHEN M.SNO = 1 THEN L.linenumber         ELSE              @MAXLINE + ROW_NUMBER() OVER (PARTITION BY CASE WHEN M.SNO<>1                 THEN 1 END ORDER BY M.CODE ,M.UNIT)         END LINE_NUMBER , M.code , M.unit FROM #lines L INNER JOIN (     SELECT ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY(UNIT)) SNO,*     FROM #map )M ON L.code = M.code 

Result:

FileId  LINE_NUMBER code    unit 1       1           A       c1 1       2           B       c3 1       4           A       c2 1       5           B       c4 1       6           B       c5 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: