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.
| Author |
Topic |
|
BigEuan
Starting Member
1 Post |
 Posted - 2013-10-24 : 05:04:55
|
| New guy here, hello.OK, this one is officially doing my head in.I have 3 tables, dbo.Fibre_Analysis, which stores staff IDs in a field called [Analysed by]. You can have more than one analyst for a given project. What I am trying to do is to take all analysts for a given project and place their names into a second table/ field ([dbo].[Surveys].[Analysed_by] (field names similar, but note the underscore in this one).As the 'names' in the first table are stored as integers I need to first get the actual names from a third table by joining the tables on [dbo].[Staff].[staff ID] and [dbo].[Fibre Analysis].[Analysed by]. I also have to remove trailing blanks against their names for an extra added level of complication.I then need to take the resultant list of analysts names and place them into [dbo].[Surveys].[Analysed_by], but only where the project numbers match and there is not already an entry in [dbo].[Surveys].[Analysed_by].Hope all that makes sense. Here is my code, it returns Incorrect syntax near 'NULL'. If I comment out the final line just to see if it makes a difference it returns Incorrect syntax near 'Project Number'.What am I doing wrong!??!DECLARE @Str NVARCHAR(50) UPDATE [dbo].[Surveys] SET [Analysed_by] = COALESCE(@Str + ', ', '') + (SELECT RTRIM([Name])FROM [dbo].[Staff]JOIN [dbo].[Fibre Analysis] ON [Staff ID] = [Analysed by]JOIN [dbo].[Surveys]ON [dbo].[Fibre Analysis].[Project Number] = [dbo].[Surveys].[Project Number]WHERE [dbo].[Fibre Analysis].[Analysed by] IS NULL |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-24 : 08:47:45
|
code will look like belowUPDATE tSET Analysed_By = u.xFROM [dbo].[Surveys] tCROSS APPLY (SELECT STUFF(SELECT ',' + Name FROM [dbo].[Staff] s JOIN [dbo].[Fibre Analysis] f ON s.[Staff ID] = f.[Analysed by] WHERE f.[Project Number] = t.[Project Number] FOR XML PATH (''),1,1,''))u(x)WHERE t.[Analysed_by] IS NULL------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|
|
|
|
Update Query