How to convert Table data to JSON value in SQL Server using column values as nodes?

We can use dynamic SQL. I am deleting rows from @Example table, so if you want to use my solution and do not delete rows from main table, you have to create additional table and insert all values. It should work for multiple different values in columns ThirdPartyInterfaceData and ThirdPartyInterfaceName. SQL Server 2016 is needed.

FIRST WAY (with WHILE loop)

DECLARE @Example TABLE 
    (
    ThirdPartyInterfaceData VARCHAR(10)
    ,ThirdPartyInterfaceName VARCHAR(10)
    ,Name VARCHAR(512)
    ,Value VARCHAR(800)
    )

INSERT INTO @Example VALUES('SMS','PLIVO','SYSTEM_SMSAuthID','MAMDQ1ODNJN2JMMZMWZD')
INSERT INTO @Example VALUES('SMS','PLIVO','SYSTEM_SMSAuthToken','YWU5NTlhNzgxYTA1OWY4NTFkMTM4NWY4ZjM5Y2Zl')
INSERT INTO @Example VALUES('SMS','PLIVO','SYSTEM_SMSFrom','Venue Metro')
INSERT INTO @Example VALUES('SMS','PLIVO','SYSTEM_SMSStatusMonitorURL','http://www.google.com')
INSERT INTO @Example VALUES('SMS','PLIVO','SYSTEM_SMSStatusMonitorURLMethod','POST')
INSERT INTO @Example VALUES('SMS','PLIVO','SYSTEM_SMSRestAPIVersion','v1')


DECLARE @sql NVARCHAR(MAX)
DECLARE @value VARCHAR(800)
DECLARE @name VARCHAR(512)
DECLARE @ThirdPartyInterfaceData VARCHAR(10)
DECLARE @ThirdPartyInterfaceName VARCHAR(10)
DECLARE @new bit = 1 --We have to recognize when to add element ""ThirdPartyInterfaceName":"PLIVO","
DECLARE @start bit = 1 --This variable is only used in first iteration of WHILE
DECLARE @json NVARCHAR(MAX)

SET @sql = 'SELECT @json = (SELECT '

/*
    We are going to delete rows from table @Example, so we can use EXISTS in WHILE
*/
WHILE EXISTS(SELECT * FROM @Example)
BEGIN
    SELECT TOP 1
        @new = CASE WHEN @start = 0 THEN CASE 
                        WHEN @ThirdPartyInterfaceData IS NOT NULL AND @ThirdPartyInterfaceName IS NOT NULL 
                            AND (@ThirdPartyInterfaceData <> ThirdPartyInterfaceData OR @ThirdPartyInterfaceName <> ThirdPartyInterfaceName) 
                                THEN 1
                                ELSE 0
                        END
                ELSE 1 END,
        @ThirdPartyInterfaceData = ThirdPartyInterfaceData,
        @ThirdPartyInterfaceName = ThirdPartyInterfaceName,
        @name = name,
        @value = value

    FROM @Example
    ORDER BY ThirdPartyInterfaceData, ThirdPartyInterfaceName, Name

    SET @start = 0

    IF @new = 1
    BEGIN
        SET @sql = @sql + '''' +  @ThirdPartyInterfaceName + '''  AS ''ThirdPartyInterfaceData.' + @ThirdPartyInterfaceData + '.ThirdPartyInterfaceName'', '
    END

    /*
        Adding next element into JSON
    */
    SET @sql = @sql + '''' + @value + '''' + ' AS ''ThirdPartyInterfaceData.' + @ThirdPartyInterfaceData + '.' + @name + ''', '

    /*
        Deleting current row
    */
    DELETE FROM @Example WHERE @ThirdPartyInterfaceData = ThirdPartyInterfaceData AND
        @ThirdPartyInterfaceName = ThirdPartyInterfaceName AND
        @name = name AND
        @value = value

END

/*
    Deleting last, unnecessary comma and adding ' FOR JSON PATH'
*/
SET @sql = SUBSTRING(@sql,1,LEN(@sql) - 1) + ' FOR JSON PATH)'


/*
    Executing sql, setting JSON into variable
*/
EXECUTE sp_executesql @sql, N'@json nvarchar(max) OUTPUT', @json = @json OUTPUT

/*
    You can do whatever you want with this variable (insert into table etc.)
*/
SELECT @json

SECOND WAY (without WHILE loop):

DECLARE @Example TABLE 
    (
    ThirdPartyInterfaceData VARCHAR(10)
    ,ThirdPartyInterfaceName VARCHAR(10)
    ,Name VARCHAR(512)
    ,Value VARCHAR(800)
    )

INSERT INTO @Example VALUES('SMS','PLIVO','SYSTEM_SMSAuthID','MAMDQ1ODNJN2JMMZMWZD')
INSERT INTO @Example VALUES('SMS','PLIVO','SYSTEM_SMSAuthToken','YWU5NTlhNzgxYTA1OWY4NTFkMTM4NWY4ZjM5Y2Zl')
INSERT INTO @Example VALUES('SMS','PLIVO','SYSTEM_SMSFrom','Venue Metro')
INSERT INTO @Example VALUES('SMS','PLIVO','SYSTEM_SMSStatusMonitorURL','http://www.google.com')
INSERT INTO @Example VALUES('SMS','PLIVO','SYSTEM_SMSStatusMonitorURLMethod','POST')
INSERT INTO @Example VALUES('SMS','PLIVO','SYSTEM_SMSRestAPIVersion','v1')

DECLARE @sql NVARCHAR(MAX) = 'SELECT @json = (SELECT '
DECLARE @json NVARCHAR(MAX)

SELECT
    @sql = @sql + JSON_Part
FROM (SELECT
            ThirdPartyInterfaceData,
            ROW_NUMBER() OVER (PARTITION BY ThirdPartyInterfaceData, ThirdPartyInterfaceName ORDER BY ThirdPartyInterfaceData, ThirdPartyInterfaceName, Name) AS Number,
            '''' + Value + ''' AS ''ThirdPartyInterfaceData.' + ThirdPartyInterfaceData + '.' + Name + ''',' AS JSON_Part
        FROM @Example
        UNION ALL
        SELECT DISTINCT
            ThirdPartyInterfaceData,
            0 AS Number,
            '''' + ThirdPartyInterfaceName + '''  AS ''ThirdPartyInterfaceData.' + ThirdPartyInterfaceData + '.ThirdPartyInterfaceName'', ' AS JSON_Part
        FROM @Example) a
ORDER BY ThirdPartyInterfaceData, Number

/*
    Deleting last, unnecessary comma and adding ' FOR JSON PATH)'
*/
SET @sql = SUBSTRING(@sql,1,LEN(@sql) - 1) + ' FOR JSON PATH)'


PRINT @sql


/*
    Executing sql
*/

EXECUTE sp_executesql @sql, N'@json nvarchar(max) OUTPUT', @json = @json OUTPUT

SELECT @json