July 25, 2011 at 9:05 pm
I am trying to put together all the comment from one table that has many row of a specific ID to an other table, with a unique row of this ID. What is the best way to do it?
I tried Merge:
Merge TargetTable
Using SourceTable as ST
ON ST.Target_ID = ID_Target
when MATCHED then update set AllComment = AllComment + ', '+ ST.Comment;
But I got this error:
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
So I tried :
Merge TargetTable
Using (Target_ID, Comment FROM SourceTable GROUP BY Target_ID) as ST
ON ST.Target_ID = ID_Target
when MATCHED then update set AllComment = AllComment + ', '+ ST.Comment;
But I got this error:
Column 'SourceTable.Comment' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I tried the 'With' clause, but without success
Any idea?
July 27, 2011 at 10:54 am
Try adding Comment to the GROUP BY clause:
GROUP BY Target_ID, Comment
Todd Fifield
July 27, 2011 at 10:59 am
What you really need is an aggregate function that does string concatenation. You can build one in CLR and use it like you would use SUM or AVG. Then your code could look something like this:
Merge TargetTable
Using (SELECT Target_ID, CONCATENATE(Comment) AS Comment FROM SourceTable GROUP BY Target_ID) as ST
ON ST.Target_ID = ID_Target
when MATCHED then update set AllComment = AllComment + ', '+ ST.Comment;
Notice that CONCATENATE does not exists, so that is the CLR function you would need to create yourself. If you don't want to use CLR, you can use the not so pretty FOR XML PATH construction like this:
MERGE TargetTable
USING (
SELECT
Target_ID,
LEFT(AllComments, LEN(AllComments)-1) AS AllComments
FROM (
SELECT distinct Target_ID,
(
SELECT Comment + ', '
FROM SourceTable st
WHERE st.Target_ID = s.Target_ID
FOR XML PATH('')
) AS AllComments
FROM SourceTable s
) t
) ST
ON ST.Target_ID = ID_Target
WHEN MATCHED THEN
UPDATE SET AllComment = AllComment + ', '+ ST.AllComments
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID_Target,AllComment)
VALUES (ST.Target_ID, ST.AllComments);
The interesting part which gives you the source data you need, is this:
SELECT distinct Target_ID,
(
SELECT Comment + ', '
FROM SourceTable st
WHERE st.Target_ID = s.Target_ID
FOR XML PATH('')
) AS AllComments
FROM SourceTable s
/Sjang
July 27, 2011 at 7:05 pm
tfifield (7/27/2011)
Try adding Comment to the GROUP BY clause:GROUP BY Target_ID, Comment
Todd Fifield
Change nothing on the error message from the merge...
Thanks for trying to help me!
July 27, 2011 at 7:27 pm
Geniiius (7/27/2011)
What you really need is an aggregate function that does string concatenation. You can build one in CLR and use it like you would use SUM or AVG.
I have no idea what is or how to do a CLR , but I am pretty sure it might have been the way to do it
I tried the 'not so pretty' and it works perfectly, except for the
UPDATE SET AllComment = AllComment + ', '+ ST.AllComments
I removed the first 'AllComment + ', '+ ' (for some reason, it was putting nothing in the column maybe because all the cell in the target column was NULL) and that was it! 🙂
Thank you very much for your help!
July 28, 2011 at 7:46 am
This was removed by the editor as SPAM
July 28, 2011 at 7:35 pm
stewartc-708166 (7/28/2011)
You could also have used CASE AND IS NULL, e.g.SET AllComments = CASE WHEN AllComments IS NULL THEN ST.AllComments ELSE AllComments + ', ' + ST.AllComments END
There is a lot to learn: never knew that you could place a "Case" into a 'set' command!
Thanks for the hint:-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply