Pass value one by one to the query condition and combine multiple rows

Question

I want to get all the role_ids based on the permission id from the #permission table with the matching permissionId in the #source_audit table Value column.

And then combine all the role_id for a given permission_id in a single row.

Any help is much appreciated

create table #permission (
    [permission_id] char(6) not null
)
   
insert into #permission
    values
    ('-336'),
    ('-12016'),
    ('-12014')
    
create table #source_audit
(
    role_id int, 
    value nvarchar(max)
)
    
insert into #source_audit
    values 
    ( 2453, '{"PermissionId":-336, "ConstraintTypeId":7}'),
    ( 2003, '{"PermissionId":-336, "ConstraintTypeId":7}'),
    ( 2123, '{"PermissionId":-12016, "ConstraintTypeId":7}'),
    ( 2893, '{"PermissionId":-14567, "ConstraintTypeId":7}')
    

Query tried so far and not working

    select srae.*, x.Permission_Id
    from #source_audit as srae
    inner join #permission as x
        on srae.value like '%' + CONVERT(varchar(32), permission_id) + '%' 
        and srae.value like '%"ConstraintTypeId":7%'

Result Table

permission_id	role_id
-336	2453, 2003
-12016	2123

Answer 1

Your #source_audit.value data is JSON, so we can use OPENJSON on it to pull out the values.

It appears you want to match on absolute values of permission_id (ignoring negative sign).

SELECT
  p.permission_id,
  roles = STRING_AGG(s.role_id, ', ')
FROM permission p
JOIN source_audit s
    CROSS APPLY OPENJSON(s.value)
        WITH ( PermissionId int, ConstraintTypeId int ) j
  ON ABS(j.PermissionId) = ABS(p.permission_id) AND j.ConstraintTypeId = 7
GROUP BY
  p.permission_id;

On SQL Server 2016 or earlier, you need to use the FOR XML method

SELECT
  p.permission_id,
  roles = STUFF((
      SELECT CONCAT(', ', s.role_id)
      FROM source_audit s
      CROSS APPLY OPENJSON(s.value)
        WITH ( PermissionId int, ConstraintTypeId int ) j
      WHERE ABS(j.PermissionId) = ABS(p.permission_id)
        AND j.ConstraintTypeId = 7
      FOR XML PATH(''), TYPE
    ).value('text()[1]','nvarchar(max)'), 1, LEN(', '), '')
FROM permission p;

SQL Fiddle