SQL, return comma separated list of each value from SELECT

Question

I am making an express web app and as part of it I am querying an SQL database that has a table of users (dbo.tbl_user with data such as name, role, email etc.

I want to query the DB so that a list of names are returned in comma separated values. When I use 'SELECT name FROM dbo.tbl_user' I get:

[
    {
        "name": "Tom"
    },
    {
        "name": "Dick"
    },
    {
        "name": "Harry"
    }
]

however I want

[
    "Tom","Dick","Harry"
]

Searching online I can only find ways of getting back [{"Tom,Dick,Harry"}] which is not what I want as the values are not separated out. Any help would be greatly appreciated.

see if “https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv” answers your question. — Mark, Jun 14 '20 at 22:17
Hi, I dont want a single text string, instead multiple comma separated text strings — micheal, Jun 14 '20 at 22:19
When you use `SELECT name FROM dbo.tbl_user`, you are simply asking for a resultset that has one column per row, which is the `name` column. That is why you get three separate records--it's what you asked for. You need to look at ways of concatenating these row results into a single result value that suits your JSON requirement. — Reg Edit, Jun 14 '20 at 22:30
The original query returns the _JSON representation of the result set_, per that query method used (it would be different if using a local SqlConnection in code, which uses a ResultSet instead..). This is likely a fixed format/structure, independent of data. Perform a transformation in the consumer to convert (after JSON deserialization) the array-of-objects to array-of-name-strings. — user2864740, Jun 14 '20 at 22:42

score 0 · Accepted Answer · answered Jun 14 '20 at 23:09

Why don't you simply use JS to get what you want from what you got from the query?

const sqlResult = [
    {
        "name": "Tom"
    },
    {
        "name": "Dick"
    },
    {
        "name": "Harry"
    }
];

const onlyNamesArr = sqlResult.map(i => i.name);   // returns ["Tom", "Dick", "Harry"]

SQL, return comma separated list of each value from SELECT

1 Answers1