Need help concatenating text columns

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)

Need help concatenating text columns

Author

Topic

surefooted
Posting Yak Master

188 Posts

Posted - 2005-01-21 : 15:50:46

Hi,

My problem is that I need to search through a table and concatenate text columns from various rows. I am running into a problem declaring the correct amount of varchars and of breaking the text up into the variables. I thought I saw an article here on this, but now can't find it. Here is the structure and some sample data.


CREATE TABLE [Comments] (
	[CO] [tinyint] NOT NULL ,
	[CLAIM_NO] [int] NOT NULL ,
	[COM_TYPE] [tinyint] NOT NULL ,
	[SEQ] [int] NOT NULL ,
	[COMMENT] [text] NULL ,
	[TRANS_DATE] [datetime] NULL ,
	[USERID] [varchar] (15) NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

insert comments
select 1, 12345, 1, 1, 'This is the first comment in this set', getdate(), 'surefooted' union all
select 1, 12345, 1, 2, 'This is the second comment in this set', getdate(), 'surefooted' union all
select 1, 12345, 1, 3, 'This is the third comment in this set', getdate(), 'surefooted' union all
select 1, 12345, 1, 4, 'This is the fourth comment in this set', getdate(), 'surefooted' union all
select 1, 12345, 1, 5, 'This is the fifth comment in this set', getdate(), 'surefooted' union all
select 1, 12345, 2, 1, 'This is the first of the next comment in this set', getdate(), 'surefooted' union all
select 1, 12345, 2, 2, 'This is the second of the next comment in this set', getdate(), 'surefooted' union all
select 1, 12345, 2, 3, 'This is the third of the next comment in this set', getdate(), 'surefooted' union all
select 1, 12345, 2, 4, 'This is the fourth of the next comment in this set', getdate(), 'surefooted' union all
select 1, 12345, 2, 1, 'This is the first of the third comment in this set', getdate(), 'surefooted' union all
select 1, 12345, 2, 2, 'This is the second of the third comment in this set', getdate(), 'surefooted'

This is what I need to get :
[code]1 12345 1 This is the first comment in this set This is the second comment in this set This is the third comment in this set This is the fourth comment in this set This is the fifth comment in this set 2005-01-21 15:46:18.100 surefooted[code]

All as one row. Please help if you can, thanks in advanced.

-Jon
Should still be a "Starting Member"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-21 : 17:49:54

You can make your life a LOT easier by NOT using text for the comment column, especially since you're sequencing comments already. Use varchar(8000) and either let your end-user Tolstoys cry, or add a little bit of code to parse their spew into 8000 byte chunks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources