CODEWITHSUNDEEP
sqlsql-servert-sqlstring-aggregation
Hardik Mer
Hardik Mer

Reputation: 836

Alternative to STRING_AGG in with SQL

I have a table as below

| 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  |

when I use STRING_AGG like this which is accurate and returns the desired result

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;

It will return like this

| 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      |

On production I have SQL Server 2014 running and had to work on alternative for STRING_AGG which is not supported on older version

here is alternative I created 

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;

it returns like this

| 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      |

What I am doing wrong with second query?

Upvotes: 3

Views: 42395

Answers (1)

Thom A
Thom A

Reputation: 96016

A some what blind guess, but I think this is the correct answer. you needed to ensure the subquery was properly correlated:

SELECT deviceID,
       UserID,
       STUFF((SELECT ' | ' + sq.activityName + ' - ' + CONVERT(varchar(20),sq.createdDate, 0)
              FROM tbl_DailyLogMaster sq
              WHERE DLM.UserID = sq.UserId
                AND DLM.DeviceID = sq.DeviceID
                AND sq.createdDate >= CONVERT(date, GETDATE())
                AND sq.createdDate < DATEADD(DAY, 1, CONVERT(date, GETDATE()))
              ORDER BY CreatedDate
              FOR XML PATH(''),TYPE).value('.','varchar(MAX)'),1,3,'') AS tag --As yuou have no leading separator, no need for STUFF
FROM tbl_DailyLogMaster DLM
WHERE DLM.createdDate >= CONVERT(date, GETDATE())
  AND DLM.createdDate < DATEADD(DAY, 1, CONVERT(date, GETDATE()))
GROUP BY UserID,
         DeviceID;

Upvotes: 10

Related Questions