SQL Server query to return missing line numbers of range as blank in resultset

Question

I have a simple issue where I have a table with some rows missing and need these rows to be displayed as blank in my SQL response.

Table reference below (BomNarration):

I   N   Narration
-----------------------------------------
1   1   PRODUCTION OVERSTATED ON JOBCARD
2   1   WORK CENTER NOT LOADED
3   1   REVERSE
4   1   alkjdflkdjflkajdflkjdflsjkdf
5   1   ADD PAPER
5   3   LOST03/10/19 ISGAC
6   1   04/10/19 ISGACL PAPER WILL ONLY BE AVAILBLE 999999

This shows column:

1. I as the ID Number
2. N as the Line Number
3. Narration as the comment string

I need to use a query to return all lines (including missing Line Numbers) For example if ID 5 is pulled up, I need to return number 1, 2 and 3.

The problem here is there is no line 2 so i need the query to fill in the missing line with a blank comment

Expected result:

I   N   Narration
-----------------------------
5   1   ADD PAPER
5   2   
5   3   LOST03/10/19 ISGAC

NOTE: that results will always start with 1 and can be up to 50.

If this is possible with CTE's it would be a great advantage as this would by part of a much larger query referencing 4 other tables to return a single dataset.

This part of the query would be used to create a string based concatenated result with indicators to be used as line breaks (example below uses the '|' symbol) per Comment line.

As mentioned above, if I can get a result like below, it would be even better.

Expected result:

I   ConcatNarration
----------------------------------------------------------------
5   ADD PAPER|{Blank Line Number here as ''}|LOST03/10/19 ISGAC

I hope this makes sense.

Answer 1

Below should get the end result even though it's a bit long winded. Had help from Concatenate column string

declare @tbl table ( i int, n int, Narration varchar(100) )

insert into @tbl (i, n, Narration)
select 1, 1, 'PRODUCTION OVERSTATED ON JOBCARD'
union all
select 2, 1, 'WORK CENTER NOT LOADED'
union all
select 3, 1, 'REVERSE'
union all
select 4, 1, 'alkjdflkdjflkajdflkjdflsjkdf'
union all
select 5, 1, 'ADD PAPER'
union all
select 5, 3, 'LOST03/10/19 ISGAC'
union all
select 6, 1, '04/10/19 ISGACL PAPER WILL ONLY BE AVAILBLE 999999'

-- create table with sequential numbers declare @tblResults table(i int, n int, Narration varchar(100))

declare @StartNumber integer, @EndNumber integer select @StartNumber = 1, @EndNumber = 50

insert into @tblResults (i, n, Narration)
select c.i, c.numbers, isnull(e.Narration, '') as Narration
from
(
select a.numbers, b.i
from
(select distinct (@StartNumber + number) as Numbers from master..spt_values where number between @StartNumber - 1 and @EndNumber - 1) a
cross join (select distinct i from @tbl) b
) c
left join (select i, max(n) n from @tbl group by i) d on d.i = c.i and d.n >= c.numbers
left join @tbl e on e.i = c.i and e.n = c.Numbers
where d.n is not null;

SELECT
i
,STUFF((
SELECT IIF(idx.n = 1, '', ' | ') + Narration
FROM @tblResults idx
WHERE tbl.i = idx.i
ORDER BY n
FOR XML PATH ('')), 1, 1, ''
) idx
FROM @tblResults tbl
GROUP BY i

Answer 2

If still looking for an answer without temp tables or variables..

declare @tbl table (
    i int,
    n int,
    Narration varchar(100)
)

insert into @tbl (i, n, Narration)
select 1,   1,   'PRODUCTION OVERSTATED ON JOBCARD'
union all
select 2,   1,   'WORK CENTER NOT LOADED'
union all
select 3,   1,   'REVERSE'
union all
select 4,   1,   'alkjdflkdjflkajdflkjdflsjkdf'
union all
select 5,   1,   'ADD PAPER'
union all
select 5,   3,   'LOST03/10/19 ISGAC'
union all
select 6,   1,   '04/10/19 ISGACL PAPER WILL ONLY BE AVAILBLE 999999'

-- create table with sequential numbers
declare @tblResults table(i int, n int, Narration varchar(100))

declare @StartNumber integer, @EndNumber integer
select @StartNumber = 1, @EndNumber = 50;

with SeqNumbers(seqnum)
as
(
    select 1 as seqnum
    union all
    select seqNum + 1 as seqnum
    from SeqNumbers
    where seqnum < @EndNumber
)

SELECT
    i
    ,STUFF((
        SELECT IIF(idx.numbers = 1, '', ' | ') + Narration
        FROM 

    (select c.i, c.numbers, isnull(e.Narration, '') as Narration
        from
        (
            select a.numbers, b.i
            from
            (select distinct seqnum as Numbers from SeqNumbers) a
            cross join (select distinct i from @tbl) b
        ) c 
        left join (select i, max(n) n from @tbl group by i) d on d.i = c.i and d.n >= c.numbers
        left join @tbl e on e.i = c.i and e.n = c.Numbers
        where d.n is not null
    ) idx
WHERE tbl.i = idx.i
ORDER BY numbers
FOR XML PATH ('')), 1, 1, '') idx
FROM (select c.i, c.numbers, isnull(e.Narration, '') as Narration
from
(
    select a.numbers, b.i
    from
    (select distinct seqnum as Numbers from SeqNumbers) a
    cross join (select distinct i from @tbl) b
) c 
left join (select i, max(n) n from @tbl group by i) d on d.i = c.i and d.n >= c.numbers
left join @tbl e on e.i = c.i and e.n = c.Numbers
where d.n is not null) tbl
GROUP BY i