Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    13

    Unanswered: concatenation / group by problem

    Hello does anybody knows a simple SQL answer on the following question:
    presume the following table:
    reference.|.productID..|..description
    C1...........|.... 2211.....|....10F 15V
    C3...........|.... 2211.....|....10F 15V
    C6...........|.... 1392.....|....22F 10V
    R3...........|.... 0100.....|....10K
    R4...........|.... 0100.....|....10K
    R2...........|.... 0130.....|.... 4K7

    The result should be...

    productID..|.. references..|.. description..|.. amount
    2211.........|.. C1,C3........|.. 10F 15V....|.. 2
    1392.........|.. C6.............|.. 22F 10V....|.. 1
    0100.........|.. R3,R4........|.. 10K............|.. 2
    0130.........|.. R2.............|.. 4K7............|.. 1


    Seems to be an ordenairy SUM and GROUP BY query
    but the problem is the concatenation of the references.
    Im not sure this is possible with pure SQL
    Who has the solution?

    Thanks
    Robert
    Last edited by Edadcon; 01-10-06 at 20:56.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    No, this is not generally possible.
    But you may get close, e.g. when you have an upper bound on the number of rows per group.
    This is actually a FAQ; see also the thread in http://www.dbforums.com/showthread.php?postid=4507450

    Assuming an upper bound of 3, the following would give something close to what you want (where tbl is your table):
    Code:
    SELECT A.productID, A.description,
           min(
           A.reference ||
           CASE WHEN B.reference IS NULL THEN ' ' ELSE
             ', ' || B.reference ||
             CASE WHEN C.reference IS NULL THEN ' ' ELSE
               ', ' || C.reference END END)
           AS references
    FROM   tbl AS A
           LEFT OUTER JOIN tbl AS B
           ON A.productID = B.productID
              AND A.description = B.description
              AND A.reference < B.reference
           LEFT OUTER JOIN tbl AS C
           ON A.productID = C.productID
              AND A.description = C.description
              AND B.reference < C.reference
    GROUP BY A.productID, A.description
    Last edited by Peter.Vanroose; 01-11-06 at 05:03.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Oct 2004
    Posts
    13
    Hi Peter,

    Many thanks for your advice.
    This is an SQL solution indeed, but for my application,
    where probably 40 references can exist for one component type, it doesnt fit.
    I have to search for script or programming solutions.
    As the matter of fact Cosmo suggested a VBA script on the Access forum that is worth to take a look at.
    I'm not experienced in VBA but I managed to make it work.
    Best regards

    Robert (edadcon)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it's really difficult in general

    however, it's trivially easy in MySQL, which has the GROUP_CONCAT function

    why did i mention MySQL? because you posted in the non-specific SQL forum without mentioning which database system you're using

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2004
    Posts
    13
    oops you're perfectly right.
    I used access to build this database but we do use MYSQL as well!
    I'd better chosen the last one instead. Probably I will switc anyway, it is not so hard to convert the access datyabase to MySQL.
    Best regards
    Robert (edadcon)

Posting Permissions

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