title: Custom aggregate function (concat) in SQL Server
body: <p>Question: I want to write a custom aggregate function that concatenates string on group by.</p>
  
  <p>So that I can do a </p>
  
  <pre><code>SELECT SUM(FIELD1) as f1, MYCONCAT(FIELD2)  as f2
  FROM TABLE_XY
  GROUP BY FIELD1, FIELD2
  </code></pre>
  
  <p>All I find is SQL CRL aggregate functions, but I need SQL, without CLR.</p>
  
  <p><br />
  <br />
  <strong>Edit:1</strong> <br />
  The query should look like this:</p>
  
  <pre><code>   SELECT SUM(FIELD1) as f1, MYCONCAT(FIELD2)  as f2
      FROM TABLE_XY
      GROUP BY FIELD0
  </code></pre>
  
  <p><br />
  <br />
  <strong>Edit 2:</strong><br />
  It is true that it isn't possible without CLR. <br />
  However, the subselect answer by astander can be modified so it doesn't XML-encode special characters.</p>
  
  <p>The subtle change for this is to add this after "FOR XML PATH":
  ,</p>
  
  <pre><code> TYPE 
                    ).value('.[1]', 'nvarchar(MAX)') 
  </code></pre>
  
  <p>Here a few examples</p>
  
  <pre><code>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 
                    ',äöü&lt;&gt;' + RM_NR AS [text()] 
              FROM T_Room 
              WHERE RM_Status = 1 
              ORDER BY RM_NR 
              FOR XML PATH('') 
  
        ) AS XmlEncodedNoNothing  
  
  
        ,
        SUBSTRING
        (
              (
                    SELECT 
                          ',äöü&lt;&gt;' + RM_NR  AS [data()] 
                    FROM T_Room 
                    WHERE RM_Status = 1 
                    ORDER BY RM_NR 
                    FOR XML PATH('')
              )
              ,2
              ,10000
        ) AS XmlEncodedSubstring  
  
  
        ,
        ( 
              STUFF 
              ( 
                    ( 
                          SELECT ',äöü&lt;&gt;' + RM_NR + CHAR(10) 
                          FROM T_Room 
                          WHERE RM_Status = 1 
                          ORDER BY RM_NR 
                          FOR XML PATH, TYPE 
                    ).value('.[1]', 'nvarchar(MAX)') 
                    , 1, 1, '' 
              ) 
        ) AS XmlDecodedStuffInsteadSubstring   
  </code></pre>