4

How to print names in string list in a single row?

Suppose that I have data like this: 

SELECT XName FROM X_Table --(table has other columns too)

XName
-------------
Foo
Bar

How to get values like this:

["foo", "bar"]
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
Chenna
  • 2,383
  • 3
  • 20
  • 36
  • @G.Stoynev I checked your duplicate question suggestion, but I'm unable to get all the result in one single row, any help on that? – Chenna Nov 28 '18 at 06:17

2 Answers2

2

I think you are looking at combination of JSON conversion and de-conversion syntax like below

See working demo

Declare @js nvarchar(max)

SET @js= (SELECT XName FROM X_Table for json path)

select * from  OPENJSON (@js) WITH ( Xname nvarchar(max) N'$.XName')

And if you want to avoid using a variable @js you can do 

select * from  OPENJSON 
((SELECT XName FROM XTable for json path))-- note double parenthesis
WITH ( Xname nvarchar(max) N'$.XName')
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
2

From the example given in G.Stoynev link, you can do the following:

SELECT JSON_QUERY
(
    (
        SELECT JSON_QUERY
        (
            '[' + STUFF(( SELECT ',' + '"' + convert(varchar(10), XName) + '"' 
            FROM dbo.X_Table
            FOR XML PATH('')),1,1,'') + ']' 
        ) Categories  
        FOR JSON PATH , WITHOUT_ARRAY_WRAPPER 
    ), '$.Categories' 
)
Chenna
  • 2,383
  • 3
  • 20
  • 36
gotqn
  • 42,737
  • 46
  • 157
  • 243