Combine multiple row into one with line breaks using sql

[Shaza]

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
[TABLE="width: 487"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]BatchName[/TD]
[TD]UserID[/TD]
[TD]Remarks[/TD]
[/TR]
[TR]
[TD]Batch1[/TD]
[TD]200123[/TD]
[TD]MK, 1-Aug-2019,users pending[/TD]
[/TR]
[TR]
[TD]Batch1[/TD]
[TD]200123[/TD]
[TD]Jason,1-Aug-2019,users engage[/TD]
[/TR]
[TR]
[TD]Batch2[/TD]
[TD]200526[/TD]
[TD]Maria, 1-Aug-2019, attain users details[/TD]
[/TR]
[TR]
[TD]Batch3[/TD]
[TD]200564[/TD]
[TD]MK, 1-Aug-2019,explain details to user[/TD]
[/TR]
[TR]
[TD]Batch4[/TD]
[TD]200118[/TD]
[TD]Maria, 1-Aug-2019, users engage[/TD]
[/TR]
[TR]
[TD]Batch4[/TD]
[TD]200118[/TD]
[TD]MK, 1-Aug-2019, users not answer[/TD]
[/TR]
[TR]
[TD]Batch4[/TD]
[TD]200267[/TD]
[TD]MK, 1-Aug-2019, user ask for follow up[/TD]
[/TR]
[TR]
[TD]Batch4[/TD]
[TD]200267[/TD]
[TD]Jason,1-Aug-2019,users engage[/TD]
[/TR]
</tbody>[/TABLE]

Expected Results
[TABLE="width: 487"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]BatchName[/TD]
[TD]UserID[/TD]
[TD]Remarks[/TD]
[/TR]
[TR]
[TD]Batch1[/TD]
[TD]200123[/TD]
[TD]MK, 1-Aug-2019,users pending
Jason,1-Aug-2019,users engage[/TD]
[/TR]
[TR]
[TD]Batch2[/TD]
[TD]200526[/TD]
[TD]Maria, 1-Aug-2019, attain users details[/TD]
[/TR]
[TR]
[TD]Batch3[/TD]
[TD]200564[/TD]
[TD]MK, 1-Aug-2019,explain details to user[/TD]
[/TR]
[TR]
[TD]Batch4[/TD]
[TD]200118[/TD]
[TD]Maria, 1-Aug-2019, users engage
MK, 1-Aug-2019, users not answer[/TD]
[/TR]
[TR]
[TD]Batch4[/TD]
[TD]200267[/TD]
[TD]MK, 1-Aug-2019, user ask for follow up
Jason,1-Aug-2019,users engage[/TD]
[/TR]
</tbody>[/TABLE]

 

[alansidman]

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

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]

ID​

[/td][td]

BatchName​

[/td][td]

UserID​

[/td][td]

Remarks​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

2​

[/td][td]

1​

[/td][td]

Batch1​

[/td][td]

200123​

[/td][td]

MK, 1-Aug-2019,users pending​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

3​

[/td][td]

2​

[/td][td]

Batch1​

[/td][td]

200123​

[/td][td]

Jason,1-Aug-2019,users engage​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

4​

[/td][td]

3​

[/td][td]

Batch2​

[/td][td]

200526​

[/td][td]

Maria, 1-Aug-2019, attain users details​

[/td][td]

​

[/td][td]

BatchName​

[/td][td]

UserID​

[/td][td]

Remarks​

[/td][/tr]


[tr][td]

5​

[/td][td]

4​

[/td][td]

Batch3​

[/td][td]

200564​

[/td][td]

MK, 1-Aug-2019,explain details to user​

[/td][td]

​

[/td][td]

Batch1​

[/td][td]

200123​

[/td][td]

Jason,1-Aug-2019,users engage​

[/td][/tr]


[tr][td]

6​

[/td][td]

5​

[/td][td]

Batch4​

[/td][td]

200118​

[/td][td]

Maria, 1-Aug-2019, users engage​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

MK, 1-Aug-2019,users pending​

[/td][/tr]


[tr][td]

7​

[/td][td]

6​

[/td][td]

Batch4​

[/td][td]

200118​

[/td][td]

MK, 1-Aug-2019, users not answer​

[/td][td]

​

[/td][td]

Batch2​

[/td][td]

200526​

[/td][td]

Maria, 1-Aug-2019, attain users details​

[/td][/tr]


[tr][td]

8​

[/td][td]

7​

[/td][td]

Batch4​

[/td][td]

200267​

[/td][td]

MK, 1-Aug-2019, user ask for follow up​

[/td][td]

​

[/td][td]

Batch3​

[/td][td]

200564​

[/td][td]

MK, 1-Aug-2019,explain details to user​

[/td][/tr]


[tr][td]

9​

[/td][td]

8​

[/td][td]

Batch4​

[/td][td]

200267​

[/td][td]

Jason,1-Aug-2019,users engage​

[/td][td]

​

[/td][td]

Batch4​

[/td][td]

200118​

[/td][td]

Maria, 1-Aug-2019, users engage​

[/td][/tr]


[tr][td]

10​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

MK, 1-Aug-2019, users not answer​

[/td][/tr]


[tr][td]

11​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

200267​

[/td][td]

Jason,1-Aug-2019,users engage​

[/td][/tr]


[tr][td]

12​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

MK, 1-Aug-2019, user ask for follow up​

[/td][/tr]


[tr][td]

13​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[/table]

 

[stumac]

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.

 

[james_lankford]

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

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
  )
)

 

[stumac]

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.