We help IT Professionals succeed at work.

SQL concatenate column in one-to-many

coperations07
on
443 Views
Last Modified: 2012-05-11
Hi,

I need to populate a column in a table with multiple values from another table.  The values need to be comma seperated.  I think I could do this with a cursor, but I'm trying to find a different way.  What else can I try?

I've tried this:
update #component_string
       set a.cmpt_agin = Stuff((Select ',' + b.cmpt_agin From component b where a.part_key = b.part_key For XML Path('')), 1, 1, '')
       from #component_string a

And I've tried this:
/*update #component_string
       set a.cmpt_agin = isnull(a.cmpt_agin + ', ', '') + b.cmpt_agin_nbr
       from #component_string a,
            component b
       where a.part_key = b.part_key*/
Comment
Watch Question

CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
You update "a" the alias instead

update a
       set a.cmpt_agin = Stuff((Select ',' + b.cmpt_agin From component b where a.part_key = b.part_key For XML Path('')), 1, 1, '')
       from #component_string a

Author

Commented:
I'm getting an error that says: incorrect syntax near 'Path'

I'm querying a Sybase database btw.

Author

Commented:
Is there anything else I can try besides a cursor?
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Sorry the syntax you showed was for SQL Server (or looks like), so I thought I could help. I don't have Sybase to help you.

Author

Commented:
hmmm..k   It probably is for SQL Server, but I did see the stuff function on the sybase site as well.  I shall keep tinkering with it. thanks.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
The cursort worked fine. Never could get the Stuff function to work.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.