[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

SQL concatenate column in one-to-many

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*/
0
coperations07
Asked:
coperations07
  • 5
  • 2
1 Solution
 
cyberkiwiCommented:
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
0
 
coperations07Author Commented:
I'm getting an error that says: incorrect syntax near 'Path'

I'm querying a Sybase database btw.
0
 
coperations07Author Commented:
Is there anything else I can try besides a cursor?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
cyberkiwiCommented:
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.
0
 
coperations07Author 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.
0
 
coperations07Author Commented:
I ended up just using a cursor to accomplish what I needed.  It doesn't seem to be affecting performance any.
0
 
coperations07Author Commented:
The cursort worked fine. Never could get the Stuff function to work.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now