0

Warning I'm a Newbie so Sorry if there is anything wrong with the question or the explanation ...

I have a table 'XYZ' with a list of Attachments (OrigFileName) and a field UniqueAttchID that is also on the header table 'ABC' to record the link so you can query which Attachments relate to the record.

I need to bring the results of all records where UniqueAttchID is equal on the Header and add them back into the header 'ABC' a field called 'udAttch' which is a Memo field formatted with a , separator

This is to get around a limitation of the reporting functionality available to me as I can only use an actual field from the Database not a related table.

Current Setup:- 

XYZ Table
UniqueAttchID    OrigFileName
----------      -------------
18181818181   |   Filename1
18181818181   |   Filename2
18181818181   |   Filename3
18181818182   |   Filename1

ABC Table - 
Description|Field2|UniqueAttchID|
test item  |test  |18181818181
Test item 2|test2 |18181818182

Desired result:-
(XYZ table would remain unchanged) 

ABC Table - 
Description|Field2|UniqueAttchID|udAttch|
test item  |test  |18181818181  |Filename1, Filename2, Filename3|
Test item 2|test2 |18181818182  |Filename1|

I've tried using COALESCE however this doesn't give me a separate record for each UniqueAttchID just one for all records, and SELECT DISTINCT only produced the first record in OrigFileName

I can then generate a Stored Procedure to run as required and update the record when new files are added as attachments.

mortb
  • 9,361
  • 3
  • 26
  • 44
Brennyc
  • 3
  • 1
  • 2
    What database system are you using? There is no ISO SQL standard way of doing this, so the solution will depend on which system you are using. – mortb May 14 '13 at 08:11
  • Take a look at http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string for example – mortb May 14 '13 at 09:17

1 Answers1

0

Please try:

select 
    *, 
    STUFF((SELECT ',' + OrigFileName
       FROM XYZ b WHERE b.UniqueAttchID=a.UniqueAttchID
        FOR XML PATH(''),type).value('.','nvarchar(max)'),1,1,'') AS [udAttch]
From ABC a
TechDo
  • 18,398
  • 3
  • 51
  • 64