#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    1
    Rep Power
    0

    Getting comma-separated string?


    Hi all, I am new to this forum and looking for help. I need to write a function that would return all the task codes from table tbl_task as a string of comma separated values but I am really stuck... I am working on an offline database in MS Access 2010. My table looks like this:
    ID desc
    1 2314
    2 2485
    3 8745
    4 6874
    5 8974

    With this query:
    SELECT ',' + tbl_task.desc
    FROM tbl_task
    ORDER BY tbl_task.desc;

    I get a following output:
    Expr1000
    ,2314
    ,2485
    ,6874
    ,8745
    ,8974

    which is fine, but I need to put them in one string. What's the best way to do it? Any help appreciated!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,439
    Rep Power
    1688
    Is that mySQL? I don't recall seeing the + being used as a string concatenator before. If it is mySQL GROUP_CONCAT might do what you want, but it has length limits associated with it. It'd probably be best to do it outside of mySQL. A lot of languages give the capability of joining (or splitting) an array.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Originally Posted by SimonJM
    Is that mySQL?
    nope, he said it was microsoft access 2010

    thread moved to databases forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,439
    Rep Power
    1688
    Originally Posted by r937
    nope, he said it was microsoft access 2010

    thread moved to databases forum
    *facepalm* For some reason I totally mis-read that!
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    103
    Rep Power
    3
    You could use a cursor to loop through all the IDs, concatenate them into a long string separated by Commas and return the String.
    http://www.codeproject.com/Tips/277847/How-to-use-Cursor-in-Sql
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Originally Posted by Cameron0960
    You could use a cursor...
    in microsoft access 2010? are you sure?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    103
    Rep Power
    3
    Originally Posted by r937
    in microsoft access 2010? are you sure?
    No lol.. I did some looking around and I guess SQL is pretty limited in MSAccess. Most of the examples I found required Visual Basic or C# code to manipulate the data and return a result. So, you could go that route, but it really depends on how your application is setup.

IMN logo majestic logo threadwatch logo seochat tools logo