Fill in missing rows in a table SQL

Question

I have a report that needs the top 18 id codes for each case. Some cases only have all 18 rows and some only have a few. Here is an example of the output:

Case       idcode          value
 2            3             122
 2            6              52 
 2            15            121
 3            1             111 
 3            3             555
 3            6             322

What I need the output to have is 18 rows per record (idcodes 1-18) and to put "none" for the value if it is added. What is the best way to add in the missing rows if I do not know which ones are missing ahead of time?

Here is my query:

SELECT 
    rcl.CaseCaseId as Case, cce.StringValue as Value, cce.CorpIdCodeId as idcode
FROM         
    CaseIdCodeEntry AS cce 
INNER JOIN
    CorpIdCodes AS cid ON cce.CorpIdCodeId = cid.CorpIdCodeId 
INNER JOIN
    PhdRpt.ReportCaseList_542 AS rcl ON cce.CaseCaseId = rcl.CaseCaseId
WHERE
    (cce.CorpIdCodeId < 19)

Answer 1

I would use a recursive CTE to auto-generate a numbered list of 1-18, and then LEFT JOIN off of that. Then use a CASE statement to adjust the Value field.

;WITH cte AS
(   SELECT DISTINCT CaseCaseId AS CaseID, 1 AS idcode
    FROM PhdRpt.ReportCaseList_542 UNION ALL
    SELECT CaseID, idcode+1 FROM cte WHERE idcode < 18 )
SELECT  cte.CaseID AS [Case], 
        CASE WHEN cce.CorpIdCodeId IS NULL THEN 'None' ELSE cce.StringValue END AS Value, 
        cte.idcode AS idcode
FROM cte
LEFT JOIN CaseIdCodeEntry cid ON cid.CorpCodeId = cte.idcode
LEFT JOIN CorpIdCodes cid ON cce.CorpIdCodeId = cid.CorpIdCodeId
LEFT JOIN PhdRpt.ReportCaseList_542 rcl ON cce.CaseCaseId = rcl.CaseCaseId

Answer 2

Try this seems works fine

create table #temp(iCase int, idcode int,value int)
Insert into #temp values(2,3,122)
Insert into #temp values(2,6,52)
Insert into #temp values(2,15,121)
Insert into #temp values(3,1,11)
Insert into #temp values(3,3,555)
Insert into #temp values(3,6,322)


create table #Val(Id int)

declare @count int =1

while (@count<=18)
begin
    insert into #Val values(@count)
    set @count=@count+1
end

DECLARE @CaseId INT
DECLARE @DataCursor CURSOR
SET @DataCursor = CURSOR FOR
SELECT distinct iCase
From #temp
OPEN @DataCursor
FETCH NEXT
FROM @DataCursor INTO @CaseId
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #temp
    SELECT @CaseId,Id,null
    FROM #Val
    WHERE Id NOT IN (
    SELECT idcode
    FROM #temp
    WHERE iCase=@CaseId )

FETCH NEXT
FROM @DataCursor INTO @CaseId
END
CLOSE @DataCursor
DEALLOCATE @DataCursor


Select * from #temp