Microsoft SQL Server 2008

Re: Can I "flatten" multiple rows into one line in a #temp table?

miteshaegis — Fri, 27 Jul 2012 12:09:09 GMT

We want to display record in one line in sql than use the select query in sql server.

select table id, stuff( ( select ' | ' + date +': ' + note from #t t2 where t2.id = t1.id for xml path('') ), 1,4, '') note from #table table1

Re: Can I "flatten" multiple rows into one line in a #temp table?

eralper — Thu, 12 Jul 2012 08:57:56 GMT

Hello ncccapt,
Please review the SQL concatenation tutorial at http://www.kodyaz.com/articles/concatenate-using-xml-path.aspx

And here is sample sql concatenate query that will help you solve your question.

select distinct id, stuff( ( select ' | ' + date +': ' + note from #t t2 where t2.id = t1.id for xml path('') ), 1,3, '') note from #t t1

Can I "flatten" multiple rows into one line in a #temp table?

ncc1701capt — Wed, 06 Jun 2012 04:38:00 GMT

I have a table containing notes for leads like this:
ID DATE NOTE
--- ---------- ------------------------
10 1/1/12 New Lead
10 2/8/12 set meeting
15 2/4/12 New Lead
15 3/9/12 set meeting
15 5/7/12 signed contract

I would like to create a #temp table in a Stored Proc that holds the data like this:
ID NOTE
---- --------------------------------------------------------------------------
10 1/1/12: New Lead | 2/8/12: Set Meeting
15 2/4/12: New Lead | 3/9/12: Set meeting | 5/7/12: signed contract

where I concatenate the date+': '+ note+' | '+... and the next date, note, etc for as many rows as there are for a given ID in the original notes table (there may be different # of rows for different IDs, obviously)...
Is this possible?

Thanks in advance!