Results 1 to 5 of 5

Thread: Combine multiple row into one with line breaks using sql
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Combine multiple row into one with line breaks using sql

    Hi, Could anyone assist me. Is it possible to merge multiple rows into one with line breaks.
    in this cases i need to combine remarks column with using batch name and userid as unique value.

    Current cases
    BatchName UserID Remarks
    Batch1 200123 MK, 1-Aug-2019,users pending
    Batch1 200123 Jason,1-Aug-2019,users engage
    Batch2 200526 Maria, 1-Aug-2019, attain users details
    Batch3 200564 MK, 1-Aug-2019,explain details to user
    Batch4 200118 Maria, 1-Aug-2019, users engage
    Batch4 200118 MK, 1-Aug-2019, users not answer
    Batch4 200267 MK, 1-Aug-2019, user ask for follow up
    Batch4 200267 Jason,1-Aug-2019,users engage

    Expected Results
    BatchName UserID Remarks
    Batch1 200123 MK, 1-Aug-2019,users pending
    Jason,1-Aug-2019,users engage
    Batch2 200526 Maria, 1-Aug-2019, attain users details
    Batch3 200564 MK, 1-Aug-2019,explain details to user
    Batch4 200118 Maria, 1-Aug-2019, users engage
    MK, 1-Aug-2019, users not answer
    Batch4 200267 MK, 1-Aug-2019, user ask for follow up
    Jason,1-Aug-2019,users engage

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,940
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combine multiple row into one with line breaks using sql

    How about importing your table into Excel and creating a Pivot Table.

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    1
    ID
    BatchName
    UserID
    Remarks
    2
    1
    Batch1
    200123
    MK, 1-Aug-2019,users pending
    3
    2
    Batch1
    200123
    Jason,1-Aug-2019,users engage
    4
    3
    Batch2
    200526
    Maria, 1-Aug-2019, attain users details
    BatchName
    UserID
    Remarks
    5
    4
    Batch3
    200564
    MK, 1-Aug-2019,explain details to user
    Batch1
    200123
    Jason,1-Aug-2019,users engage
    6
    5
    Batch4
    200118
    Maria, 1-Aug-2019, users engage
    MK, 1-Aug-2019,users pending
    7
    6
    Batch4
    200118
    MK, 1-Aug-2019, users not answer
    Batch2
    200526
    Maria, 1-Aug-2019, attain users details
    8
    7
    Batch4
    200267
    MK, 1-Aug-2019, user ask for follow up
    Batch3
    200564
    MK, 1-Aug-2019,explain details to user
    9
    8
    Batch4
    200267
    Jason,1-Aug-2019,users engage
    Batch4
    200118
    Maria, 1-Aug-2019, users engage
    10
    MK, 1-Aug-2019, users not answer
    11
    200267
    Jason,1-Aug-2019,users engage
    12
    MK, 1-Aug-2019, user ask for follow up
    13
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    Board Regular
    Join Date
    Jul 2010
    Posts
    449
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combine multiple row into one with line breaks using sql

    If you know the number of rows then you can achieve this using IIF statements, however, it is a messy solution - especially if its likely to change.

    Otherwise its not possible using only SQL - you will need a UDF for this. there are plenty of examples such as this one if you search for MS Access concatenate rows.

  4. #4
    Board Regular
    Join Date
    Jan 2009
    Posts
    1,164
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combine multiple row into one with line breaks using sql

    I was thinking maybe something like this
    didn't test it out though

    Code:
     
    
    select 
      tbl_1.BatchName, 
      tbl_1.UserID,
      tbl_1.Remarks & vbcrlf & tbl_2.Remarks 
    from 
      your_table as tbl_1 
        left join 
          your_table as tbl_2
          on
          tbl_1.BatchName = tbl_2.BatchName 
          and 
          tbl_1.UserID = tbl_2.UserID
    where 
    (
      (
        tbl_1.Remarks <> tbl_2.Remarks
      )
    )

  5. #5
    Board Regular
    Join Date
    Jul 2010
    Posts
    449
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combine multiple row into one with line breaks using sql

    Dont think that would work, it would you show a maximum of 2 remarks on a row and each one would be duplicated A/B, B/A. If there was more than 2 then you would get each combination twice.

    Also vbcrlf wont be recognised within access SQL: chr(13) & chr(10) should do the same job though.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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