Edit Question - KnowledgeHub

Title Body

Question: I want to write a custom aggregate function that concatenates string on group by. So that I can do a SELECT SUM(FIELD1) as f1, MYCONCAT(FIELD2) as f2 FROM TABLE_XY GROUP BY FIELD1, FIELD2 All I find is SQL CRL aggregate functions, but I need SQL, without CLR. Edit:1 The query should look like this: SELECT SUM(FIELD1) as f1, MYCONCAT(FIELD2) as f2 FROM TABLE_XY GROUP BY FIELD0 Edit 2: It is true that it isn't possible without CLR. However, the subselect answer by astander can be modified so it doesn't XML-encode special characters. The subtle change for this is to add this after "FOR XML PATH": , TYPE ).value('.[1]', 'nvarchar(MAX)') Here a few examples DECLARE @tT table([A] varchar(200), [B] varchar(200)); INSERT INTO @tT VALUES ('T_A', 'C_A'); INSERT INTO @tT VALUES ('T_A', 'C_B'); INSERT INTO @tT VALUES ('T_B', 'C_A'); INSERT INTO @tT VALUES ('T_C', 'C_A'); INSERT INTO @tT VALUES ('T_C', 'C_B'); INSERT INTO @tT VALUES ('T_C', 'C_C'); SELECT A AS [A] , ( STUFF ( ( SELECT DISTINCT ', ' + tempT.B AS wtf FROM @tT AS tempT WHERE (1=1) --AND tempT.TT_Status = 1 AND tempT.A = myT.A ORDER BY wtf FOR XML PATH, TYPE ).value('.[1]', 'nvarchar(MAX)') , 1, 2, '' ) ) AS [B] FROM @tT AS myT GROUP BY A SELECT ( SELECT ',äöü<>' + RM_NR AS [text()] FROM T_Room WHERE RM_

Tags (comma-separated) Cancel