Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.8k views
in Technique[技术] by (71.8m points)

sql server - 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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...