row text concatenation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

row text concatenation

Author

Topic

johncage
Starting Member

1 Post

Posted - 2014-11-03 : 04:42:04

Hi,
I have a table with those fields:
Table No int
Entry No int
Line No int
Text Varchar

Table No filter is fixed
There can be of course multiple Entry No in this case
(ERP people will recognize Ms Dynamics)

I need to have as a result a concatenation of the text field by entry.
The line order must be respected.
so basically:

Entry No
Full text

How can i achieve this?

Sample
Origin
169 75222 10000 blalba1
169 75222 20000 blabla2
169 75222 30000 blabla3

Result
75222 blabla1blabla2blabla3

Many thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-03 : 09:13:08

[code]
declare @t table (table_no int, entry_no int, line_no int, linetext varchar(20))
insert into @t (table_no, entry_no, line_no, linetext) values
(169, 75222, 10000, 'blalba1'),
(169, 75222, 20000, 'blabla2'),
(169, 75222, 30000, 'blabla3')

select table_no, entry_no, c.linetext
from @t t
cross apply (
select '' + linetext
from @t u
where t.table_no = u.table_no
and t.entry_no = u.entry_no
for xml path('')
) c(linetext)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources