Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    12

    Lightbulb Unanswered: multiple row result concatenation

    Table1
    ID,Description
    1,"Vowels"
    2,"Others"

    Table2
    ID,Table1ID,Letter
    1,1,"A"
    2,2,"L"
    3,2,"K"
    4,1,"U"
    5,1,"E"
    6,2,"G"
    7,1,"I"

    How can I query the two tables to come up with a resulting table of:

    ID,Description,Letter
    1,"Vowels","A, U, E, I"
    2,"Others","L, K, G"

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Check this thread just ignore the flaming (some people should seriously consider a personal shrink ) :

    http://www.dbforums.com/t993022.html
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Posts
    12
    I don't think this solution works. What I need to be able to do is use the coalesce function inside of a selection list. Basically, I want to take table2 data and return the table1id and the description field after using the coalesce.

    Below is an example of how the code should somewhat look to get the desired functionality. However, the code below does not work and errors out.

    select t1.id,
    t1.description,
    (select @List = coalesce(@list + ', ', '') + t2.description
    from table2 t2
    where t2.table1id = t1.id)
    from table1 t1
    Last edited by lgraunke; 10-06-04 at 10:22.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I know that the solution works, and it became somewhat a defacto standard for dealing with cases like yours. It has known limitations, but if you don't abuse it, - it will work for you as well.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Posts
    12
    I guess I am still struggling with this and how to get it to work. I understand the concept of what the function is doing but am having a hard time getting it work in the above case. Could you use the sample data from the original request and see if you could come up with an query for me that does what I am looking for?

    Any additional help would be greatly appreciated.

  6. #6
    Join Date
    Nov 2003
    Location
    Akld, NZ
    Posts
    38
    Hmm Yeah I am more than intruigued on how to get this to work within another query also (ie using the 2 table scenario at the top).
    I have been able to make the concatenation process work alone - however intigrating it as part of another is rather puzzling - and it appears as though no one else has an answer.....yet....

    Anyone?
    Sideways see's more scenery

  7. #7
    Join Date
    Nov 2003
    Location
    Akld, NZ
    Posts
    38
    Hi,

    Just to update this - I did a bit of thinking about how to skin this cat and have come up with the following:

    Have setup a user defined function using the script like
    declare @str varchar(8000)
    set @str = ''
    select @str = @str + case when @str = '' then cast(field_name as varchar(25)) else ',' + cast(field_name as varchar(25)) end from [Table a] where [table a].fieldX = @field_value
    **where @field_value is a variable for the function

    Then all I had to do was run a select like:
    select field_1, function_name(field_1)
    from table_b

    this worked for me at least - not sure on the efficiency of it, but it seemed okay.

    Let me know if this has helped, or if it has just confused the matter.
    Sideways see's more scenery

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That was the idea, I didn't realize it was not as straight forward as I thought...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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