Combine multiple row into one with line breaks using sql

Shaza

New Member
Joined
Jan 3, 2018
Messages
11
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
BatchNameUserIDRemarks
Batch1200123MK, 1-Aug-2019,users pending
Batch1200123Jason,1-Aug-2019,users engage
Batch2200526Maria, 1-Aug-2019, attain users details
Batch3200564MK, 1-Aug-2019,explain details to user
Batch4200118Maria, 1-Aug-2019, users engage
Batch4200118MK, 1-Aug-2019, users not answer
Batch4200267MK, 1-Aug-2019, user ask for follow up
Batch4200267Jason,1-Aug-2019,users engage

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Expected Results
BatchNameUserIDRemarks
Batch1200123MK, 1-Aug-2019,users pending
Jason,1-Aug-2019,users engage
Batch2200526Maria, 1-Aug-2019, attain users details
Batch3200564MK, 1-Aug-2019,explain details to user
Batch4200118Maria, 1-Aug-2019, users engage
MK, 1-Aug-2019, users not answer
Batch4200267MK, 1-Aug-2019, user ask for follow up
Jason,1-Aug-2019,users engage

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
 
Upvote 0
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.
 
Upvote 0
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
  )
)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top