Generate new line number for each mapping

Question

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 ?

Answer 1

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

Answer 2

It may not be as simple as you would have liked. Posting it anyway.

DECLARE @MaxLine INT;

SELECT @MaxLine = MAX(LineNumber)
FROM   lines;


IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp;

SELECT   l.fileId ,
         l.linenumber ,
         l.code ,
         MIN(m.unit) AS unit
INTO     #temp
FROM     #Lines l
         JOIN #map m ON l.code = m.code
GROUP BY l.fileId ,
         l.linenumber ,
         l.code;

SELECT *
FROM   #temp
UNION
SELECT l.fileId ,
       @MaxLine + ROW_NUMBER() OVER ( PARTITION BY l.fileId
                                      ORDER BY l.code ) ,
       l.code ,
       m.unit
FROM   #LINES l
       JOIN #map m ON l.code = m.code
       LEFT JOIN #temp t ON l.code = t.code
                            AND m.unit = t.unit
WHERE  t.code IS NULL;