locked
SQL to return a single row from a multi-row result set - as a comma list RRS feed

  • Question

  • In the VBA script, using ODBC interface to the database, the result set is expected to be a single value as a comma - separated list in a string, like: "80212,84321,9215,80144"

    I expect it would be some kind of aggregate function. Can you help me with the kind of SQL query. The actual data in the tables has some government regulations around it, so I have to be a bit circumspect.

    Thank you,


    Jon Jacobs, There are 10 kinds of people: those who understand binary and those who don't

    Friday, July 31, 2020 12:08 AM

All replies

  • In modern SQL you can use STRING_AGG.

    A sample table and a query:

    declare @sample_table as table ( x int )


    insert into @sample_table values

       ( 80212 ),

       ( 84321 ),

       ( 9215  ),

       ( 80144 )


    select string_agg(x, ', ') as Result

    from @sample_table

    /*

    Result

    --------------------------

    80212, 84321, 9215, 80144

    */


    Give more details if you need something else.


    • Edited by Viorel_MVP Friday, July 31, 2020 11:29 AM
    Friday, July 31, 2020 11:28 AM
  • Well, I thought with ODBC, it would have its own syntax, but I now see I was mistaken. ODBC just passes the query to the underlying database driver, in this case an Oracle driver.

    Since I didn't specify the database type, I can see you assumed SQL Server. Since I thought ODBC had its own syntax, I didn't believe it was relevant. I was wrong.

    So it rejects string_agg. So I will have to try with LIST_AGG, which is rather difficult. I never could get it right in a straight query to the Oracle Client.


    Jon Jacobs, There are 10 kinds of people: those who understand binary and those who don't

    Tuesday, August 4, 2020 3:48 PM