Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    Unanswered: Concatenate Distinct Field Values From Multiple Rows Into One Field

    I have a problem in where I need to concatenate a field from multiple rows into one field and then seperate the values with a column delimeter. The thing that complicates my problem is the fact that some of the values that I am trying to concatenate however have multiple values that have already been concatenated. The following example shows a more detailed example of what I am trying to achieve.

    A B C D
    ---------------------------------------------
    A000011111|JONES, SMITH|4/21/2009|APPLE
    A000011111|JONES, SMITH|4/21/2009|ORANGE
    A000011111|JONES, SMITH|4/21/2009|APPLE, ORANGE
    A000022222|JOHN, DOE|4/21/2009|APPLE
    A000022222|JOHN, DOE|4/21/2009|APPLE, BANANA, PEAR
    A000022222|JOHN, DOE|4/21/2009|CHERRY


    DESIRED OUTPUT (DISTINCT VALUES ONLY FOR LAST COLUMN)

    A B C D
    ---------------------------------------------
    A000011111|JONES, SMITH|4/21/2009|APPLE, ORANGE
    A000022222|JOHN, DOE|4/21/2009|APPLE, BANANA, PEAR, CHERRY


    Thanks,

    DB

  2. #2
    Join Date
    Dec 2008
    Posts
    135
    if ur using 2005 and above try this one
    Code:
    DECLARE @t TABLE (A VARCHAR(32),B VARCHAR(32), C VARCHAR(32), D VARCHAR(32))
    insert into @t select
    'A000011111','JONES, SMITH','4/21/2009','APPLE' union all select
    'A000011111','JONES, SMITH','4/21/2009','ORANGE' union all select
    'A000011111','JONES, SMITH','4/21/2009','APPLE, ORANGE' union all select
    'A000022222','JOHN, DOE','4/21/2009','APPLE' union all select
    'A000022222','JOHN, DOE','4/21/2009','APPLE, BANANA, PEAR' union all select
    'A000022222','JOHN, DOE','4/21/2009','CHERRY'
    
    select distinct a,b,c,stuff((Select distinct ','+ d from @t where t.a = a for xml path('')),1,1,'') from @t t

  3. #3
    Join Date
    Apr 2009
    Posts
    25
    Alternatively use a Cursor object to loop through each distinct 'A code' and update a string that will contain your concatonated fruits.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you're on a version earlier that 2000 then you may have to resort to cursors.
    If you're on 2000 then the XML method won't work, however there is a UDF method you can implement to the same effect. If you require this method then post back.
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2009
    Posts
    349
    oh goodness no, I do not have time to dummy up an example but look at this and say "set based processing"... (I know I left out some stuff but you should be able to get the idea)..
    Code:
    select @lastContractID=-1, @LastSiteLocationID=-1, @List=' '	
    UPDATE #Results
    	SET
    		@List =	ProductString = CASE 
    			WHEN @LastContractID <> ContractID or @LastSiteLocationID<>SiteLocationID THEN ProductString
    			ELSE @List + ', ' + ProductString
    			End,
    		@LastContractID = ContractID, 
    		@LastSiteLocationID = SiteLocationID

  6. #6
    Join Date
    Mar 2009
    Posts
    349
    and mine works on all versions SQL Server

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sean, I'm afraid I fail to see how to apply that to this chaps scenario - if you find the time can you expand?

    Have you noted the DISTINCT part to the question?

    From the sample data: 2 lines of John Doe's contain apples, the required output is to only have one of these.
    George
    Home | Blog

  8. #8
    Join Date
    Mar 2009
    Posts
    349
    small details. The distinctiveness is easily done with derived tables or correlated subqueries. all work in every version of SQL server I have touched.

    the main problem is the concatenation. You can do it row by row with a function or a cursor if you want but good luck doing that 1 million times in a reasonable amount of time.

    The first time I saw this technique it took me a little while to Grok it, but waiting is fullness lad.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sean,

    If you get the time tomorrow can you apply your code to the OPs scenario or knock up a complete working illustration? It would certainly help me piece together the workings of your statements and learn a fancy new trick
    George
    Home | Blog

  10. #10
    Join Date
    Dec 2008
    Posts
    135
    if ur using sql2000 version then try to use the userdefined fucntion for these

    sample example check this once
    http://www.dbforums.com/microsoft-sq...ate-names.html
    Last edited by bklr; 04-23-09 at 07:38.

Posting Permissions

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