Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Contatenate string

Posted on 2003-11-12
7
Medium Priority
?
384 Views
Last Modified: 2008-03-10
Is there a way you can concatenate string together in a group.
f.g

select max(string) from table group by KEYFIELD
is legal,

but I want to return a string list separated by ","
like 'XXX', 'XXX' for key field.

I can do it using a cursor, but can we write a function so it can be returned
automatically.

0
Comment
Question by:pwang1973
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9733829
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9733923
CREATE Function udf_GetList (@KEYFIELD int)

Returns varchar(1000)

As
Begin

Declare      @List varchar(1000)

Set @List = ''
Select      @List = @List + Value + ', '
From      Table1
Where      KEYFIELD= @KEYFIELD

-- Len:  Excludes trailing blanks.
If Len(@List) > 0
   Set @List = Left(@List, Len(@List) - 1)

Return @List

End

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9733928
Also, please maintain these old oopen questions:

1 09/11/2003 50 ASP COM+ component SESSION VARIABLE.  Open Web Languages
2 09/15/2003 155 ACTIVEX - VB control validateEvent  Open VB Controls
3 09/30/2003 50 A utlity to search all fields in all tab...  Open Databases

Thanks,
Anthony
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:pwang1973
ID: 9734085
Thanks Anthony:

I tried and it works, the only question left is that I try to create a function with table Name passed in as a paramter, but inside function you can not use the exec command. Is there a workaround for it?

Thanks
Pat
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 9734251
>> but inside function you can not use the exec command.<<
That is correct. I do not believe you can use the sp_executesql, either.  In any case using dynamic SQL should only be done as  a last resort.  

>>Is there a workaround for it?<<
Potentially how many distinct tables can be passed in?

Finally, please do not forget your old open questions:
These questions can be deleted as there were no contributions:
http://www.experts-exchange.com/Web/Web_Languages/Q_20735955.html
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/VB_Controls/Q_20739291.html

This question can be solved using Full-Text Search:
http://www.experts-exchange.com/Databases/Q_20753054.html
But I suggest you post a message in Community Support to have it deleted. See here:
Nobody answered my question. What do I do?
http://www.experts-exchange.com/help/closing.jsp#5

Thanks,
Anthony
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9738536
not that i'd advise using it but would the adding a remote server for the current server
and accessing the tables via openquery trick work in this scenario?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9740261
Lowfatspread,

I think you may have posted in the wrong thread.

Anthony
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question