Concatenate Records (Rows) - Help

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 2000 Forums

Transact-SQL (2000)

Concatenate Records (Rows) - Help

Author

Topic

ianfrater
Starting Member

6 Posts

Posted - 2009-03-16 : 08:04:34

I am struggling concatenating text from multiple rows.

Background: our ERP system for some reason stores text in 240 character chunks, then combines them when displayed / printed.

I want to combine export the records to a new table with a single record per text entry.

I can achieve this in msaccess but my god is slow and I am struggling with an effective way to perform this in SQL server

Table dbo.ttttxt010200_text

Fields: t_ctxt int
T_clan Char(1) Language
T_seqe Int Sequence
T_text char(240) Text Entry

Example Data:
t_text t_clan t_seqe t_text
111102 4 1 "75175250125 "
111302 4 1 "Fournisseur 240280. Attention, n'ayant en "
111303 2 1 "11:14 13/06/2005 Customer has approximately"
111303 2 2 " 13/06/2005 T/P Lynfa. be made on We"
111303 2 3 "ake place. Arranged with Steve Roode to next"
111303 2 4 "RB 10:31 24/06/2005 Lynfa confirmed visit "
111304 3 1 " Spezialrabatt
110051 2 1 "12 month service carried out on 3 machines.
111051 2 2 " and a good quality of Print. 1stept br"
111052 4 1 "75073100050. "

What i am trying to achieve is records 111303 and 111051 to appear as a single recordset.

Many thanks in advance
Ian

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 08:16:34

There are numerous posts here about concatenating recrods.

E 12°55'05.63"
N 56°04'39.26"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 08:17:00

Can you post the output just to be clear ? Also, If you can format your sample data, that'd be great.

ianfrater
Starting Member

6 Posts

Posted - 2009-03-16 : 09:17:48

Thanks

output ideally needs to be

Fields
t_ctxt
t_text

t_ctxt t_text
111102 75175250125
111302 Fournisseur 240280. Attention, n'ayant en
111303 11:14 13/06/2005 Customer has approximately 13/06/2005 T/P Lynfa. be made on We take place. Arranged with Steve Roode to next RB 10:31 24/06/2005 Lynfa confirmed visit "
111304 Spezialrabatt
111051 12 month service carried out on 3 machines and a good quality of Print. 1stept br"

111303 and 111051 are now 1 record

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-17 : 00:41:52

[code]
create a function as below and try it once
CREATE FUNCTION dbo.concat_tab_products (
@t_ctxt varchar(32)
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @return varchar(1000)

SELECT @return = Coalesce(@return + ',', '') + t_text
FROM dbo.tablename
WHERE t_ctxt = @t_ctxt

RETURN @return
END
GO

SELECT t_ctxt
, dbo.concat_tab_products(t_ctxt)
FROM dbo.tablename
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources