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:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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

1
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
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
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

LEARN MORE
TarasAuthor Commented:
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.
0
TarasAuthor Commented:
It worked very well. Thank you all very much.
0
Mark WillsTopic AdvisorCommented:
>> 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
0
Ramesh D JaiswalCommented:
Excellent best solution Ryan
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.