1

I need to combine rows where Recid is the same, but using a carriage return/linefeed between row values, rather than a comma.

See example data below:

create table #Temp
(
Companyid nvarchar(2),
Recid nvarchar(1), 
Notes nVarchar(max)
)
insert into #Temp 
Values (
'A','1','NotesA1'),
('A','1','NotesA11'),
('A','1','NotesA111'),
('A','2','NotesA2'),
('A','2','NotesA22'),
('B','1','NotesB1')
select Recid, Notes from #Temp where Companyid='A'
drop table #Temp

How do I end up with the below:

Recid    Notes
1        NotesA1
         NotesA11
         NotesA111
2        NotesA2
         NotesA22

I have tried using the following but it returns semi-colons between values:

create table #Temp
(
Companyid nvarchar(2),
Recid nvarchar(1), 
Notes nVarchar(max)
)
insert into #Temp 
Values (
'A','1','NotesA1'),
('A','1','NotesA11'),
('A','1','NotesA111'),
('A','2','NotesA2'),
('A','2','NotesA22'),
('B','1','NotesB1')

SELECT dr.Recid,
STUFF((SELECT '; ' + cast(US.notes  as nvarchar(max))
FROM #Temp US
WHERE US.Recid = DR.Recid and us.COMPANYID='A'
FOR XML PATH('')), 1, 1, '') [Notes]
FROM #Temp DR 
where (dr.Companyid='A') 
GROUP BY DR.Recid
ORDER BY dr.Recid
drop table #Temp

Returns:

Recid    Notes
1        NotesA1;NotesA11;NotesA111
2        NotesA2;NotesA22

Cheers for all help!

Naz
  • 525
  • 2
  • 9
  • 21
  • First of all, this is best left to the presentation layer and reporting software. Merging the RecID cells is trivial there. Which SQL Server version are you using? SQL Server 2017 has `STRING_AGG` for this. – Panagiotis Kanavos Mar 22 '19 at 12:19
  • Replace in your code `'; '` with `char(10)` – forpas Mar 22 '19 at 12:20
  • Hi @PanagiotisKanavos I am using SQL 2012. I would use the presentation layer but I am unable to at the moment due to security issues. Thanks for the advice, will try to get approval to do it that way in future – Naz Mar 22 '19 at 13:15
  • Hi @forpas I have tried this but I dont get a carriage return/linefeed between row values - its still a space – Naz Mar 22 '19 at 13:24
  • Check this: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=85bd3476d658e37e71ace68f21d33ba8 – forpas Mar 22 '19 at 13:27
  • Hi @forpas, it works fine now I try to run it in a new query window, cheers! – Naz Mar 22 '19 at 13:52

1 Answers1

2

You were very close. A Carriage return is ASCII character 13 and the Line break is 10, so be represented by CHAR(13) & CHAR(10) (or NCHAR(13) & NCHAR(10) for an nvarchar). Because of the way XML works, some characters might be escaped (specifally the linebreak would be shown as '&#x0D'). Therefore I prefer to use the TYPE keyword and then extract the values by using the value XML functionality (something I was only introduced to this year):

SELECT DR.Recid,
       STUFF((SELECT NCHAR(13) + NCHAR(10) + CAST(US.Notes AS nvarchar(MAX))
              FROM #Temp AS US
              WHERE US.Recid = DR.Recid
                AND US.Companyid = 'A'
             FOR XML PATH(N''),TYPE).value(N'.','nvarchar(MAX)'),1,2,N'') AS [Notes]
FROM #Temp AS DR
WHERE (DR.Companyid = 'A')
GROUP BY DR.Recid
ORDER BY DR.Recid;
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Hi @Larnu I tried this but I just get a space between each value, not a CR/LF. – Naz Mar 22 '19 at 13:18
  • Are you sure @Naz? I don't inject a space at all. If you're getting a space, are you sure it isn't in the value of`US.Notes`? – Thom A Mar 22 '19 at 13:27
  • Also, @Naz , I assume that if you are copying from SSMS you are retaining the characters https://stackoverflow.com/a/53115559/3484879 – Thom A Mar 22 '19 at 13:29
  • 1
    Or perhaps the OP is looking at the results in grid view. To see the new lines in SSMS you must view the results as text. – Sean Lange Mar 22 '19 at 13:30
  • @Naz post what you're doing, as I can't replicate this problem with your sample. The code I have, with your sample data, returns the data with carriage return/Line break delimiter. – Thom A Mar 22 '19 at 13:45
  • Also, be careful with a CSV @Naz. By default a line break is a row delimiter. – Thom A Mar 22 '19 at 13:48
  • Hi @Larnu, looks like I was being a doofus! I opened a new query window in SSMS, and the code works fine there now! For some reason it still doesnt work in the original query window, even though it is exactly the same. Thanks for the help! – Naz Mar 22 '19 at 13:51
  • Perhaps you've changed the options I linked to above since opening the window? Sometimes setting changes don't apply to any active windows. – Thom A Mar 22 '19 at 13:58
  • Yes I did change the options, but they must have only applied to the new window as you say. – Naz Mar 22 '19 at 14:58