title: Alternative to STRING_AGG in with SQL
body: <p>I have a table as below</p>
  
  <pre><code>| activityName | UserID | deviceID | createdDate             |
  |------------------------------------------------------------|
  | ON           | 1      | adddsad  |2020-01-09 00:02:59.477  |
  | OFF          | 1      | adddsad  |2020-01-09 00:50:39.857  | 
  | ON           | 2      | bdddsad  |2020-01-09 00:51:11.480  |
  | OFF          | 2      | bdddsad  |2020-01-09 00:51:19.450  | 
  </code></pre>
  
  <p>when I use <code>STRING_AGG</code> like this which is accurate and returns the desired result</p>
  
  <pre><code>SELECT STRING_AGG(activityName + ' - ' + CONVERT(varchar, createdDate), ' | ') AS tag,
         deviceID,
         UserID
  FROM (SELECT tag,
               deviceID,
               UserID
        FROM tbl_DailyLogMaster
        WHERE CONVERT(date, createdDate) = CONVERT(date, GETDATE())
        GROUP BY userID) a
  GROUP BY UserID;
  </code></pre>
  
  <p>It will return like this</p>
  
  <pre><code>| tag                                                           | deviceID  | UserID |
  |------------------------------------------------------------------------------------|
  | ON - 2020-01-09 00:02:59.477 | OFF - 2020-01-09 00:50:39.857  | adddsad   | 1      |
  | ON - 2020-01-09 00:51:11.480 | OFF - 2020-01-09 00:51:19.450  | bdddsad   | 2      |
  </code></pre>
  
  <p>On production I have SQL Server 2014 running and had to work on alternative for <strong><code>STRING_AGG</code></strong> which is not supported on older version</p>
  
  <p>here is alternative I created </p>
  
  <pre><code>SELECT deviceID,
         UserID,
         STUFF((SELECT activityName + ' - ' + CONVERT(varchar, createdDate)
                FROM tbl_DailyLogMaster
                WHERE userID = tbl_DailyLogMaster.UserID
                  AND CONVERT(date, createdDate) = CONVERT(date, GETDATE())
                ORDER BY UserID
               FOR XML PATH('')),1,1,'') AS tag
  FROM tbl_DailyLogMaster
  WHERE CONVERT(date, createdDate) = CONVERT(date, GETDATE())
  GROUP BY UserID,
           deviceID,
           UserID,
           createdDate,
           activityName;
  </code></pre>
  
  <p>it returns like this</p>
  
  <pre><code>| tag                                                                                                                                | deviceID  | UserID |
  |---------------------------------------------------------------------------------------------------------------------------------------------------------|
  | N - Jan  9 2020 12:51AMOFF - Jan  9 2020 12:51AMON - Jan  9 2020 12:02AMOFF - Jan  9 2020 12:50AM | OFF - 2020-01-09 00:50:39.857  | adddsad   | 1      |
  | N - Jan  9 2020 12:51AMOFF - Jan  9 2020 12:51AMON - Jan  9 2020 12:02AMOFF - Jan  9 2020 12:50AM | OFF - 2020-01-09 00:50:39.857  | adddsad   | 1      |
  | N - Jan  9 2020 12:51AMOFF - Jan  9 2020 12:51AMON - Jan  9 2020 12:02AMOFF - Jan  9 2020 12:50AM | OFF - 2020-01-09 00:50:39.857  | bdddsad   | 2      |
  | N - Jan  9 2020 12:51AMOFF - Jan  9 2020 12:51AMON - Jan  9 2020 12:02AMOFF - Jan  9 2020 12:50AM | OFF - 2020-01-09 00:50:39.857  | bdddsad   | 2      |
  </code></pre>
  
  <p>What I am doing wrong with second query?</p>