Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    39

    Question Unanswered: Convert multiple rows to one single row data

    hi,
    I have data which is in multiple rows and I want the data in these rows to be joined together based on a condition.
    Example:
    SKU Description Value
    1 abc 123
    1 bcd 23
    1 cde 45
    2 xyz 66
    2 fgh 20
    3 lmn 10

    And I want the data to be as

    SKU Description
    1 abc123,bcd23,cde45
    2 xyz66,fgh20
    3 lmn10

    Can we do this with one single query in ms-access.
    Please help.
    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oog. There is a quirk in SQL Server which allows you to do this, but I doubt that it would work in Access. This is generally not a good idea to do at all, but their may be a method that will work for you.

    Can you place an upper limit on the number of values that would be concatenated for each row? Say a maximum of five or six?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I think in Access one would use a user defined function on something like this. Perhaps Duane Hookoms function, Generic Function To Concatenate Child Records, found here

    http://www.rogersaccesslibrary.com/OtherLibraries.asp

    would help. I think it assumes a master table from where the SKU is derived.
    Roy-Vidar

  4. #4
    Join Date
    Jun 2004
    Posts
    39
    yes, even i thought in the same way, to create a user-defined function and use it in the code.
    can we do it using a query???

Posting Permissions

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