2

I have a database with 2 columns

A     B
--    --
X     1995
Y     2005
C     1962
D     2003

I'm trying to create a SQL statement that will take a string of comma delimited values and return a json list of values in B where any value in the string is in A

so if the comma delimited string was 'X,C' the json list would be [1995,1962]

I've been using json path to try this, but I can't get it exactly like I want it and I've been spinning my wheels for too long

This is what I've tried:

Select mt.B as json_list_b_values
 From  [dbo].[myTable] mt
Where  mt.A in (Select value From String_Split('X,C', ',')) for json path

This is the ouput:

[ {"json_list_b_values":"1995"}, {"json_list_b_values":"1962"} ]
1

If you are able to use FOR XML PATH('')

SELECT  DISTINCT
 QUOTENAME((STUFF(
    (
      SELECT ',' + try_convert(varchar(4),mt.B)
      FROM [dbo].[myTable] mt
      WHERE mt.a in ('X','C')
      FOR XML PATH('')
    ), 1, 1, '')));

If not, you could do a lot of REPLACE()ments

    SELECT  DISTINCT
 QUOTENAME(REPLACE(REPLACE(REPLACE(REPLACE((STUFF(
    (
      SELECT  mt.B as '}'
      FROM [dbo].[myTable] mt
      WHERE mt.a in ('X','C')
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ), 1, 1, '')),'"',''),'}',''),'{',''),':',''))

Result for both:

(No column name)
[1995,1962]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.