Is this too complex to do it in a query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2005)

Is this too complex to do it in a query?

Author

Topic

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-25 : 18:27:54

I'm not sure if this is too complicated to achieve via a query or if I should go ahead and write a simple util.

Basically I have 2 tables (tblProjects, tblProjectsJunction) and want to update a column in tblProjects with the data from tblProjectsJunction table.

tblProjects has columns AssociatedNos, ProjectID, ProjectName.
tblProjectsJunction has AssociatedID, ProjectID

Here is what I want to do

1. Selct * from tblProjects.
2. For each row in tblProjects, get the list of AssociatedID's from tblProjectsJunction table (using the ProjectID) and generate a comma delimited string.
3. Update the column AssociatedNos in tblProjects with comma delimited string.

Is this possible to do via sql query?

Thanks a lot

-rum23

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 01:07:52

[code]UPDATE p
SET p.AssociatedNos= LEFT(al.AssoList,LEN(al.AssoList)-1)
FROM tblProjects p
CROSS APPLY (SELECT CAST(AssociatedID AS varchar(10)) + ','
FROM tblProjectsJunction
WHERE ProjectID=p.ProjectID
FOR XML PATH(''))al(AssoList)[/code]

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-26 : 11:27:00

visakh16: You rock! Thanks a mil.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 13:49:41

quote:
Originally posted by rum23

visakh16: You rock! Thanks a mil.

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources