Alternative to STRING_AGG in with SQL

Question

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?

I really suggest starting to use whitespace and line breaks in your SQL. I also seggest you *always* declare your length, scale and precision for your data types. `CONVERT(varchar, {expression})` will cause you problems one day. — Thom A, Jan 09 '20 at 09:20
SQL Server Management Studio (SSMS) is not your SQL Server. Which version of SQL Server are you running? — Mark Rotteveel, Jan 09 '20 at 09:24
@MerHardik Your first code has a more than one error. Please check and correct that. — VBoka, Jan 09 '20 at 09:28
@Larnu I have already defined length of data. Its datetime that I am converting into varchar to concatenate two columns. — Hardik Mer, Jan 09 '20 at 15:59
*" I have already defined length of data"* No, you don't, @MerHardik : `CONVERT(varchar, createdDate)` Where do you define the length of the `varchar` there? — Thom A, Jan 09 '20 at 16:03

Thom A · Accepted Answer · 2020-01-09T16:39:23.373

9

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;

edited Jan 09 '20 at 16:39

answered Jan 09 '20 at 09:28

Thom A

88,727
11
45
75

I do not think your solution is correct. I believe the OP needs two lines. Alos please fix this line: FROM tbl_DailyLogMaster DLM into FROM tbl_DailyLogMaster DML. Cheers – VBoka Jan 09 '20 at 09:34
1

It's actually the other aliases that were wrong; force of habit to type DML (Data Manipulation Language), @VBoka . As the the start of the comment, I did say this was a blind guess. :) – Thom A Jan 09 '20 at 09:38
Sorry, was focused on the query :) – VBoka Jan 09 '20 at 09:38
@Larnu ON - Jan 9 2020 12:02AMOFF - Jan 9 2020 12:50AM can we have separator between two records? – Hardik Mer Jan 09 '20 at 16:07
Yes, what separator would you want, @MerHardik ? – Thom A Jan 09 '20 at 16:07
@Larnu Jan 9 2020 12:02AM | OFF - Jan 9 2020 12:50AM something like this – Hardik Mer Jan 09 '20 at 16:09
1

So `' | '` @MerHardik ? If so, please see the updated version. – Thom A Jan 09 '20 at 16:39

Alternative to STRING_AGG in with SQL

1 Answers1

Linked