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 ?

Moray

Your original Lines table - is it guaranteed to have only one row per code? – Physiology

@VladimirBaranov - yes. – Avaavadavat

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

Extravagance

Thanks.. looks better than mine. Only gripe is @MAXLINE, there can be more than one FileId, but that can be easily fixed. – Avaavadavat

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;

Amil

Answer 3

It seems like you want:

select m.*,
       coalesce(l.linenumber,
                c.cnt + row_number() over (partition by l.linenumber order by m.linenumber)
               ) as new_linenumber
from (select m.*,
             row_number() over (partition by code order by linenumber) as seqnum
      from #map m
     ) m left join
     #lines l
     on l.code = m.code and m.seqnum = 1 cross join
     (select count(*) as cnt from #lines) c;

That is, the new line number is either:

1. The line number in the linenumber table -- for the first appearance of each code.
2. A sequential number based on the non-matching line numbers plus the count in linenumber.

Akan

Answer 4

I would use first_value() function :

with t as (
     select l.fileid, l.code, m.unit, (case when f_value = m.unit then l.linenumber end) as lines, cnt.tcnt 
     from #lines l inner join
          (select *, first_value(unit) over (partition by code order by unit) as f_value
           from #map
          ) m
          on m.code = l.code cross join
         (select count(*) tcnt from #lines) cnt
)

select fileid, code, unit, 
       coalesce(lines, 
                tcnt + sum(case when lines is null then 1 end) over (order by unit)
               ) as linenumber
from t;

Revocable

Unfortunately we are still stuck with Sql Server 2008 !! – Avaavadavat