Results 1 to 6 of 6
  1. #1
    Join Date
    May 2008
    Posts
    97

    Unanswered: Column Concatenation

    I have 2 tables (forgive my syntax)

    BID
    =======
    BIDID INT,
    NAME NVARCHAR(10)
    PK(BIDID)

    BIDLINE
    ========
    BIDLINEID INT(10)
    BIDID INT (FK),
    PK(BIDID, BIDLINEID)

    So for each 'BID' Row there are many 'BIDLINE' Rows. What I am trying to do is: select all 'BID' Rows showing the concatenated 'BIDLINE.BIDLINEID' so.. the BIDLINEID's would be delimited list of all the bidlinesid's associated with this bidid

    RESULTS:
    ==========================
    BIDID | BIDLINEIDS

    1 | 1,2,3,4,5
    2 | 1,2,3
    3 | 1,2,3,4,5,6,7

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This should be handled by your presentation layer (front-end, report, etc).

    Whilst it can in theory be achieved in SQL it will be inefficient and could potentially fall on its butt if the recordsets get too large.
    George
    Home | Blog

  3. #3
    Join Date
    May 2008
    Posts
    97
    Quote Originally Posted by gvee View Post
    This should be handled by your presentation layer (front-end, report, etc).

    Whilst it can in theory be achieved in SQL it will be inefficient and could potentially fall on its butt if the recordsets get too large.
    This is for a report. There is no frontend. It must be in the resultset. I am willing to take the chance of it falling on its butt. I will test return time.

    Let me know the theoretical part please
    Last edited by Eric the Red; 03-14-13 at 18:30.

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    SELECT 
        b.BIDID, 
        STUFF( (SELECT ',' + CAST(l.BIDLINEID AS VARCHAR(10))
                FROM BIDLINE AS l
                WHERE l.BIDID = b.BIDID
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
                ,1, 1, '')
    FROM BID as b
    Hope this helps.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Completely untested air-code

    Code:
    ; WITH x AS (
      SELECT bidid
           , bidlineids
           , Row_Number() OVER (PARTITION BY bidid ORDER BY bidlineids) As row_num
      FROM   dbo.bidline
    )
    , y AS (
      SELECT bidid
           , bidlineids
           , row_num
           , Convert(varchar(max), bidlineids) As res
      FROM   x
      WHERE  row_num = 1
        UNION ALL
          SELECT x.bidid
               , x.bidlineids
               , x.row_num
               , Convert(varchar(max), y.bidlineids) + ', ' + x.res
          FROM   x
           INNER
            JOIN y
              ON y.bidid   = x.bidid
             AND y.row_num = x.row_num + 1
    )
    SELECT *
    FROM   y
    George
    Home | Blog

  6. #6
    Join Date
    May 2008
    Posts
    97
    Quote Originally Posted by imex View Post
    Try:

    Code:
    SELECT 
        b.BIDID, 
        STUFF( (SELECT ',' + CAST(l.BIDLINEID AS VARCHAR(10))
                FROM BIDLINE AS l
                WHERE l.BIDID = b.BIDID
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
                ,1, 1, '')
    FROM BID as b
    Hope this helps.
    I tried out this solution. And it worked.

    Also, I don't actually have these tables with this table structure. I just wanted to use a simplified example of my database to udnerstand how such a thing would be possible.

    Thanks for much for all your help

    have a great weekend!

Posting Permissions

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