Sapri s's questions

Tenho um procedimento armazenado onde obtenho alguns nomes de colunas da tabela e pego os nomes de colunas restantes dinamicamente. Eu obtenho nomes de colunas sem registros se eu não convertê-los para string JSON, mas se eu convertê-los para json_stringusar JSON AUTO, ele retorna nulo em vez de todos os nomes de colunas com um registro nulo. Como obter todos os nomes de colunas com um registro nulo aqui?

ALTER PROCEDURE [dbo].[GetVendorCriticalityInventory]
    @CategoryName VARCHAR(120) = null 
AS
BEGIN
    DECLARE @sql1 Nvarchar(Max);
    DECLARE @ques Nvarchar(max);

    DECLARE @CommonColumns NVARCHAR(MAX) = '
RID,
RequestID,
[Application/Vendor Name],
[Risk Identified],
[Application/Vendor Type],
[Date of the Request],
[IT Owner],
[Business Owner],
Requestor,
[App/Vendor Criticality],
ISOClause,
Submodule
';

    SELECT @ques = STRING_AGG(CAST(QUOTENAME(Question) AS varchar(MAX)),',') 
    FROM
        (select distinct Question 
         from TPMSMasterData 
         where TPMSCategoryName = @CategoryName) AS D

    --SET @sql1 = '
    SET @sql1 = '

    WITH LatestData AS 
    (
        select
            R.RequestID as RID,
            R.RequestIDInfo as RequestID,
            isnull(R.NameOftheTool, ''N/A'') as [Application/Vendor Name],
            RiskSummaryScore as [Risk Identified],
            isnull(convert(varchar(10), R.RequestSubmitted, 120), ''N/A'') as [Date of the Request],
            isnull(R.NameOfModule, ''N/A'') as [Application/Vendor Type],
            ''N/A'' as [IT Owner],
            isnull(R.BusinessOwners, ''N/A'') as [Business Owner],
            isnull(R.RequestCreatedByEmailID, ''N/A'') as Requestor,
            isnull(cast(R.AppCriticalityScore AS varchar), ''N/A'') AS [App/Vendor Criticality],
            isnull(A.Question, ''N/A'') as Question,
            R.ISOClause,
            R.Submodule,
            isnull(case
                       when exists (SELECT 1 
                                    FROM STRING_SPLIT(A.DataControlLup, '':'') AS sg 
                                    WHERE TRIM(sg.value) = ''LookupTable'' ) 
                           then d.lookupDisplayValue 
                       when A.DataControlLup like ''%[A-Za-z]%'' 
                           then c.LupDisplayValue 
                       else b.Data1 
                   end, 
            ''N/A'') as Data1 ,
            row_number() over (partition by B.RequestID, A.Question order by B.TPMSTransID desc) as rn
        from
            TPMSMasterData A
        join
            TPMSTransactionData B on A.TPMSID = b.TPMSID
        join
            RequestInfo R on B.RequestID = R.RequestID
        left join
            BusinessLookupTable c on TRY_CAST(b.Data1 as int) = c.BusinessLupID
        left join 
            LookupTable d on try_cast(B.Data1 AS INT) = d.LookupID
        where
            A.TPMSCategoryName = @CategoryName 
            and R.WorkFlowCompleted = 1 

) 
SELECT(
    select ' + @CommonColumns + ', ' + @ques + '
    from (
        SELECT ' + @CommonColumns + ',Question,Data1
        FROM LatestData 
        WHERE rn = 1
    ) as TPMSCategory
    pivot(max(Data1) for Question in (' + @ques + ') ) as LatestAnswer
    for json auto
) AS [my_json]
';

exec sp_executesql @sql1,N'@CategoryName VARCHAR(120)',@CategoryName
END
GO

A saída que obtenho quando não há registros para exibir é

Mas a saída que eu quero é

Esta é minha última tentativa

 SELECT @DummyColumns = STRING_AGG('NULL AS ' + TRIM(value), ', ')
 FROM STRING_SPLIT(@CommonColumns + ',' + @ques, ',');  
,
 TPMSCategory AS (
     SELECT ' + @CommonColumns + ', Question, Data1
     FROM LatestData
     WHERE rn = 1
 ),
 LatestAnswer AS (
     SELECT ' + @CommonColumns + ', ' + @ques + '
     FROM TPMSCategory
     PIVOT (
         MAX(Data1) FOR Question IN (' + @ques + ')
     ) AS PivotTable
 )
 SELECT (
     SELECT ' + @DummyColumns + ', la.*
     FROM LatestAnswer AS la
     FOR JSON PATH  
 ) AS my_json';

E recebo este erro:

A propriedade 'RID' não pode ser gerada na saída JSON devido a um conflito com outro nome de coluna ou alias. Use nomes e aliases diferentes para cada coluna na lista SELECT.

Como posso resolver esse erro de duplicação?