1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Contatenate the same field from multiple rows

Discussion in 'SQL PL/SQL' started by Buckyman, Apr 1, 2011.

  1. Buckyman

    Buckyman Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    One to many relationship: Customer table -> Interest Codes

    I need to return one row per Customer, with all their Interests concatenated in one field.

    I hope this explains my dilemma :)
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
  3. Buckyman

    Buckyman Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Appears I am not up to par, as my db is 10gR1. wm_concat & listagg would have been perfect for the job! I read the article you suggested "String Aggregation Techniques", perhaps you could suggest the simplest plan of action for my db version. Many thanks.

    Code (SQL):
    SELECT  C_Location.Address1 || ', ' || C_Location.City || ', ' || C_Region.Name || ', ' || C_Location.Postal "Address", C_BPartner.Name, C_BPartner_Location.Phone,
    (CASE SALESREP_ID WHEN 1000229 THEN 'Bart' WHEN 1000223 THEN 'Andre' WHEN 1005140 THEN 'Dan' WHEN 1005129 THEN 'Rhett' ELSE 'Unknown' END) "Territory", AD_User.Email, (the concatenated FIELD goes here AS "Interests")
    FROM C_BPartner
     LEFT OUTER JOIN C_BPartner_Location
          ON (C_BPartner.C_BPartner_ID=C_BPartner_Location.C_BPartner_ID AND C_BPartner_Location.Name='Head Office')
     LEFT OUTER JOIN C_Location
          ON (C_BPartner_Location.C_Location_ID=C_Location.C_Location_ID )
     JOIN C_Region
          ON (C_Location.C_Region_ID=C_Region.C_Region_ID)
     LEFT OUTER JOIN AD_User
          ON (AD_User.C_BPartner_ID = C_BPartner.C_BPartner_ID AND  AD_User.Name = '.')
     LEFT OUTER JOIN R_ContactInterest
          ON (AD_User.AD_User_ID=R_ContactInterest.AD_User_ID)
     JOIN R_InterestArea
          ON (R_ContactInterest.R_InterestArea_ID=R_InterestArea.R_InterestArea_ID)
    ORDER BY  C_BPartner.Name ASC
    Bloody club-oracle code kept telling my post was spam, taking out the WHERE clause allowed it to post.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I suggest you follow the link for stragg and use it.
     
  5. Buckyman

    Buckyman Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Appears I was mistaken, wm_concat is supported - at least there is no syntax error :)
    The code below produces a result, but the column Interests which is supposed to have the concatenated rows, is blank.
    I would appreciate your help.

    Dang Club Oracle still keeps saying my post is spam, so I have attached the SQL as text file.
     

    Attached Files:

  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    With all of the left outer joins is it possible that the column in question returns nothing but NULL values?
     
  7. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    It shouldn't now. Apologies for that.
     
    Buckyman likes this.
  8. Buckyman

    Buckyman Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    I have tried simple JOIN in a couple of places where I thought it was appropriate, my last version was all LEFT OUTER. But anyway, LEFT JOIN should produce the most results, no?
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    LEFT JOIN and LEFT OUTER JOIN are the same.
     
  10. Buckyman

    Buckyman Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    I know left outer is same as left join :)
    What I am after is the Interests of my contacts. Table R_ContactInterest has the Interests, but in a numerical ID form, to get the actual Interests, I have to join tables R_ContactInterest & R_InterestArea, which up to now has not yielded desired results.
    I was able to get the code to work ALMOST, I removed the last join and was able to concatenate the numerical ID's . Please refer to "sql_screen_shot.jpg". Problem is... the numerical ID's are useless to me, but I was hoping someone might see from this partial success some way to make this work.

    Code (SQL):
    SELECT  C_BPartner.Name, C_BPartner_Location.Phone, AD_User.Email,  wm_concat(R_ContactInterest.R_InterestArea_ID) Interests
    FROM C_BPartner
     LEFT OUTER JOIN C_BPartner_Location
          ON (C_BPartner.C_BPartner_ID=C_BPartner_Location.C_BPartner_ID AND C_BPartner_Location.Name='Head Office')
     LEFT OUTER JOIN AD_User
          ON (AD_User.C_BPartner_ID = C_BPartner.C_BPartner_ID AND  AD_User.Name = '.') -- each customer has a  contact named ".", don't worry if this restriction seems weird
     LEFT OUTER JOIN R_ContactInterest
          ON (R_ContactInterest.AD_User_ID = AD_User.AD_User_ID)
    WHERE C_BPartner.IsCustomer = 'Y' AND  C_BPartner.ActualLifetimeValue > 0 AND C_BPartner.IsActive = 'Y'
    GROUP BY  C_BPartner.Name, C_BPartner_Location.Phone, AD_User.Email
    ORDER BY  C_BPartner.Name ASC
     

    Attached Files:

  11. Buckyman

    Buckyman Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Well, it's a bit of a hack I think. I used CASE to convert the ID's to the actual values that I need, got rid of the last 2 joins and added a sub-SELECT. At least it works.
    Code (SQL):
    SELECT   C_Location.Address1 || ', ' || C_Location.City || ', ' || C_Region.Name || ', ' || C_Location.Postal "Address", C_BPartner.Name, C_BPartner_Location.Phone, AD_User.Email,
    (SELECT wm_concat
       
      (CASE R_ContactInterest.R_InterestArea_ID WHEN 1000015 THEN 'GTA' WHEN 1000000 THEN 'NN' WHEN 1000001 THEN 'BL' WHEN 1000002 THEN 'PC' WHEN 1000003 THEN 'VI' WHEN 1000004 THEN 'RA' WHEN 1000005 THEN 'DE' WHEN 1000006 THEN 'NL' WHEN 1000007 THEN 'TP' WHEN 1000008 THEN 'CO' WHEN 1000009 THEN 'AM' WHEN 1000010 THEN 'WW' WHEN 1000012 THEN 'FA' WHEN 1000013 THEN 'CG' WHEN 1000014 THEN 'WH' ELSE 'Unknown' END)  Interests

    FROM R_ContactInterest
    WHERE R_ContactInterest.AD_User_ID = ad_user.ad_user_ID
    ) Codes
    FROM C_BPartner
     LEFT OUTER JOIN C_BPartner_Location
          ON (C_BPartner.C_BPartner_ID=C_BPartner_Location.C_BPartner_ID AND C_BPartner_Location.Name='Head Office')
     LEFT OUTER JOIN C_Location
          ON (C_BPartner_Location.C_Location_ID=C_Location.C_Location_ID )
     JOIN C_Region
          ON (C_Location.C_Region_ID=C_Region.C_Region_ID)
     LEFT OUTER JOIN AD_User
          ON (AD_User.C_BPartner_ID = C_BPartner.C_BPartner_ID AND  AD_User.Name = '.')
    WHERE C_BPartner.IsCustomer = 'Y' AND  C_BPartner.ActualLifetimeValue > 0 AND C_BPartner.IsActive = 'Y'
    ORDER BY  C_BPartner.Name ASC