Concatenate rows

I have table tblNote in SQLServer database

with next fields

Note_ID            Note_Date             CreatedBy        Notes
1             2014-06-25 18:47:03      John Borkovic      Test Note adfadf
1             2014-06-25 18:52:28      Jim Jonson        Test Note adfadf
2             2014-06-25 18:55:05      Bill Smith        Test Notes alkdfakd
3             2014-06-25 18:58:31      Kathy Story        Test Notes alkdl
3             2014-06-25 19:27:30      Bill Smith        Test Notes abagab
......
I need script that will do merge - Concatenate those rows per Note ID to look like

Note_ID            NoteDate_1             CreatedBy_1        Notes_1                    NoteDate_2             CreatedBy_2        Note_2
1             2014-06-25 18:47:03      John Borkovic      Test Note adfadf        2014-06-25 18:52:28      Jim Jonson        Test Note adfadf
2            2014-06-25 18:55:05      Bill Smith        Test Notes alkdfakd        Null                    Null             Null
3            2014-06-25 18:58:31      Kathy Story        Test Notes alkdl        2014-06-25 19:27:30      Bill Smith        Test Notes abagab

Per one Note_ID I could have more entries here I my example I have the most two,
but it could be up to 30 entries per Note ID, so I could have 30 x 3 = 90 columns in result.

TarasAsked: 2017-12-18

Who is Participating?

LVL 56

Ryan ChongCommented: 2017-12-18

By applying Paul's solution and if we wish to specify the no of grouped columns to be returned, we can try this:

if exists(select * from sys.objects where object_id = object_id(N'tblNote') and type in (N'U'))
drop table tblNote
go
    
create table tblNote
(
	Note_ID int,
	Note_Date datetime,
	CreatedBy varchar(30),
	Notes varchar(100)	
)
insert into tblNote
values
(1, '2014-06-25 18:47:03', 'John Borkovic', 'Test Note adfadf'),
(1, '2014-06-25 18:52:28', 'Jim Jonson', 'Test Note adfadf'),
(2, '2014-06-25 18:55:05', 'Bill Smith', 'Test Notes alkdfakd'),
(3, '2014-06-25 18:58:31', 'Kathy Story', 'Test Notes alkdl'),
(3, '2014-06-25 19:27:30', 'Bill Smith', 'Test Notes abagab)');


declare @MaxCount int;
set @MaxCount = 30;

declare @SQL nvarchar(max),@i int;
 
set @i = 0;
 
while @i < @MaxCount
begin
    set @i = @i + 1;
   
    set @SQL = coalesce(@SQL + ', ', '') + 'max(case when rn = '+ cast(@i as nvarchar(10))+' then Note_Date end) as NoteDate_' + cast(@i as nvarchar(10));
    set @SQL = coalesce(@SQL + ', ', '') + 'max(case when rn = '+ cast(@i as nvarchar(10))+' then CreatedBy end) as CreatedBy_' + cast(@i as nvarchar(10));
    set @SQL = coalesce(@SQL + ', ', '') + 'max(case when rn = '+ cast(@i as nvarchar(10))+' then Notes end) as Notes_' + cast(@i as nvarchar(10));
end

set @SQL = 
	N'select Note_ID, '+ @SQL + ' ' +
	'from
	(
      select Note_ID, notes, CreatedBy, Note_Date, row_number() over(partition by Note_ID order by Note_Date desc) as rn
      from tblNote 
    ) d
	group by Note_ID'
 
print @SQL;
exec (@SQL);

Open in new window

LVL 50

PortletPaulfreelancerCommented: 2017-12-18

Never ceases to amaze me why you would want such a wide set of results, but one way to achieve this "pivot" of your data is to use "case expressions" (I'm only providing the first and last "sets" of these).

I am assuming there is something shared between the notes that I call "common_column" (it could be more then one column too) that will be used to form the rows.

I have also used a descending order of dates when calculating the rn (row number) so that the most recent notes will be in column group 1, then the next most recent, then the next most recent, ...

select
      common_column
    , max(case when rn = 1  then Notes     end) as    note_1
    , max(case when rn = 1  then CreatedBy end) as created_1
    , max(case when rn = 1  then Note_Date end) as   dated_1
...
    , max(case when rn = 30 then Notes     end) as    note_30
    , max(case when rn = 30 then CreatedBy end) as created_30
    , max(case when rn = 30 then Note_Date end) as   dated_30
from (
      select common_column, notes, CreatedBy, Note_Date
           , row_number() over(partition by common_column 
                               order by Note_Date DESC) as rn
      from tblNote 
      ) d
group by
      common_column

Open in new window

LVL 54

Mark WillsTopic AdvisorCommented: 2017-12-18

Good work Paul and Ryan ...

Just to add a different dimension, taking a step back I am a little stuck on "merge - Concatenate" and maybe it is literal.

So, when you say "merge - Concatenate" are you saying you want to have discrete columns, or, actually merge - concatenate ?

Are the number of columns important in terms of being reference-able ?

Basically, I guess what I am really asking is what is the reason for / why are you doing this ? An export maybe ?

If not needing discrete columns but want a delimited string (I have used '|' but could be anything) in a single select statement:

select cast(T1.Note_ID as varchar(10)) +'|'+ rtrim(stuff(isnull((select ' | '+convert(varchar(20),note_date,120)+ ' | ' + CreatedBy + ' | ' + replace(Notes,'|',' ') 
            from tblNote T2 where T1.Note_ID = T2.Note_ID order by T2.Note_Date for xml path('')),'  '),1,3,''))  as Notes
from tblNote T1
Group by T1.Note_ID

-- or to keep note_id as a discrete column...


select T1.Note_ID, rtrim(stuff(isnull((select ' | '+convert(varchar(20),note_date,120)+ ' | ' + CreatedBy + ' | ' + replace(Notes,'|',' ') 
            from tblNote T2 where T1.Note_ID = T2.Note_ID order by T2.Note_Date for xml path('')),'  '),1,3,''))  as Notes
from tblNote T1
Group by T1.Note_ID

Open in new window

And have some ideas on how to enhance the very good posts above. Paul and Ryan have performed most admirably....

But to move forward, we will need your feedback as to direction and intended use....

Cheers,
Mark Wills

TarasAuthor Commented: 2017-12-20

Thank you very much to all of you. Purpose of this script was to prepare result for export to excel. User wanted it on that way presented. I had to put in additional part that will calculated number of notes so the number of column can be dynamic, Here I used number 30 but in reality it will be in range from 10 to 15.
Thank you all.

TarasAuthor Commented: 2017-12-20

It worked very well. Thank you all very much.

LVL 54

Mark WillsTopic AdvisorCommented: 2017-12-20

>> Purpose of this script was to prepare result for export to excel.

Ah-ha which explains the phrase merge-concatenate. Also explains the columnar style format...

Thank You for your feedback. Much appreciated getting that clarification.

Great to see you found your solution here. It was a pleasure to be able to help, and to collaborate with 2 fine experts in Paul and Ryan.

Cheers,
Mark Wills