Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2008
    Posts
    2

    Unanswered: SQL - Multiple text values in the same field

    Hi

    Is there any way you can concatenate multiple text values in the one field? The following query....

    Code:
    select distinct name.id, education.course
    from name
    inner join education on name.id = education.id
    ...returns a new record for each course the person has completed. Is there a way that all these values could be concatenated in one field?

    Currently something like this is returned.

    ID course
    111 bachelor of arts
    111 bachelor of law
    111 master of arts

    The following would be ideal.

    ID course
    111 bachelor of arts, bachelor of law, master of arts

    Is this possible?

    Thanks in advance for any help

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not in ANSI SQL, no

    it's trivially easy in MySQL, more difficult in other database systems

    for example, in SQL Server, you'd write a user-defined function, such as this --
    http://sqlblindman.googlepages.com/c...limitedstrings
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2008
    Posts
    2
    Thanks for that . I modified the code in that link to...

    Code:
    declare @DelimitedString varchar(500)
    
    Select  @DelimitedString = isnull(@DelimitedString + ', ', '') + education.course
    From    education
    
    select distinct name.id, @DelimitedString
    from name
    inner join education on name.id = education.id
    ...it does concatenate multiple values in one field, however it includes every value contained in the course field on every record (until it runs over 500 chars ), while I only want the courses that an individual has completed. Should there be a group by in there somewhere?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by age
    I only want the courses that an individual has completed
    WHERE clause
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by r937
    not in ANSI SQL, no
    Actually, you can do that quite easily with recursive SQL, which is standardized. It can typically be found under "bill of materials".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, that's right, Knut, recursive CTE

    can you show an example please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Sure. One example can be found here: http://publib.boulder.ibm.com/infoce.../r0000879.html Instead of multiplying quantities, you can use the CONCAT (or ||) operator to deal with strings.

    Note that the SQL standard requires the additional RECURSIVE keyword, i.e. it would have to be "WITH RECURSIVE RPL ( ..."
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by r937
    oh, that's right, Knut, recursive CTE

    can you show an example please?
    See e.g. the threads http://www.dbforums.com/showthread.php?p=6248263, http://www.dbforums.com/showthread.php?t=918402 and http://www.dbforums.com/showthread.php?t=1200060
    Note that you need to specify the concatenation order in some way or the other, e.g. through a third column (or in the example it's alphabetic on the second column).
    (Aggregate SUM, COUNT etc. are order-indifferent, CONCAT isn't, that's why there is no aggregate CONCAT function.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •