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

SQL Server Development (2000)

concatenation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-11-17 : 07:50:47

adriane writes "select fieldID, combined_data = concat(textfields, ', ')
from reqst_input_data
into #TmpRequestedInfo
where fieldID = (select fieldID from MainTable)

I'm getting an error message telling me SQL does not recognize 'concat', so I'm getting a sinking feeling I will have to create a function to do this.

There can be multiple 'textfields' of varying lengths for each 'fieldID', and I would like to move them all to one large text field.

Any suggestions?"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-17 : 08:12:51

Do you need something like this?
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

adriane
Starting Member

7 Posts

Posted - 2005-11-22 : 15:02:22

Thanks for your quick reply. I appreciate it. The link got me pointed in the right direction I'm sure, but I haven't been able to get the select statement in my view to work efficiently.

'Select distinct' will make it run on and on without any result. Removing the 'distinct' will give me output, but of course they are multiple instances for every fieldID - and it still runs very slow. I've had to abort the execute every time.

I checked to make sure there is no cartesian join in the view, but I can't find any problem there. Sure wish there was an easier way to do this. Any ideas?

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-11-22 : 15:35:40

Have you tried:
select fieldid, Textfield1 + TextField2 + TestField3 as CombinedFields
from ....

I assume that your MainTable only returns 1 fieldId is that correct?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-23 : 00:17:15

Well.
How many rows does your table have?
Did you use index for the keycolumn?
If distinct from that table wont work, then copy the distinct values to other table and do query based on that table

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-23 : 00:22:33

quote:
Originally posted by druer

Have you tried:
select fieldid, Textfield1 + TextField2 + TestField3 as CombinedFields
from ....

I assume that your MainTable only returns 1 fieldId is that correct?

I think the questioner's expected result is not what you specified

It there are data like these


Id Name
1  a
1  b
1  c
2  a
2  b
Then the result should be
Id Combined_Name
1  a,b,c
2  a,b

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources