Why this STRING_AGG does not work?

1

Hello

I write:
SELECT [ID]
,STRING_AGG(SELECT [Status] ,',') AS [Status Sequence]
FROM Table1
WHERE
[Record Date] <= '2019-12-31'
and [ID] IN (1,2,3)
GROUP BY [ID]
ORDER BY [Status Date]

But it does not work. I tried many combinations of these columns but none seemed to work.

Any idea?

Thanks!

2

we have no idea what "it does not work" means
please provide details about "it does not work" provide sample data and expected result

2 Likes
3

What about:

SELECT [ID]
,STRING_AGG([Status] ,',') AS [Status Sequence]
FROM Table1
WHERE
[Record Date] <= '2019-12-31'
and [ID] IN (1,2,3)
GROUP BY [ID]

Your original statement had SELECT inside the STRING_AGG() function, which is invalid syntax:

SELECT [ID]
,STRING_AGG(SELECT [Status] ,',') AS [Status Sequence]
FROM Table1
WHERE
[Record Date] <= '2019-12-31'
and [ID] IN (1,2,3)
GROUP BY [ID]
ORDER BY [Status Date]

You also can't ORDER BY Status Date without including it in the SELECT clause.

Agree with @yosiaz's comment, you need to describe what's not working in more detail. Several of us have mentioned this to you in your previous posts.

4

Thanks but this will not aggregate the strings on chronological order based on [Status Date] which is what I want to achieve.

Apologies for the SELECT inside the STRING_AGG, it was one of the attempts to make it work.

I get the error that the [Status Date] does not exist in an aggregate function or GROUP BY.

Any idea?

5

You asked this question a month ago:

It was answered there, by linking to the documentation:

The "Arguments" and "Syntax" sections have details, and Example F demonstrates it.

Please explain why you didn't include that in your original posting. It's exactly the detail we have to constantly beg you to provide, and when you leave it out you waste everyone's time, including yours.

As I mentioned in another thread, I cannot understand if you genuinely want any help from SQLTeam. The way you ask questions, ignore the answers and follow-ups, and post the same question despite multiple warnings and comments, suggests that you're an internet troll.

If you are a troll, then stop.

If you're not a troll, you need to make more of an effort to research your question before posting, provide the details to explain exactly what you need, and not "This doesn't work". You made a big step today by explaining this in your follow-up, but you need to do more, and do it earlier. And stop posting the same question multiple times. Go back to the original and post follow-up questions there.

6 Likes
8

hi sqlor

hope this helps :slight_smile: :+1:

grouping by id is working ,,have to work on getting Order By Sequence

sample data 
drop table Table1
create table Table1
(
Status varchar(10) , 
RecordDate date , 
ID int ,
StatusDate date 
)
go
set dateformat ymd
insert into Table1(ID,RecordDate,Status,StatusDate) select 1,'2019-12-31','OK2'  ,'2020-09-10'
insert into Table1(ID,RecordDate,Status,StatusDate) select 1,'2019-12-25','OK1'  ,'2020-09-09'
insert into Table1(ID,RecordDate,Status,StatusDate) select 1,'2019-12-22','OK3'  ,'2020-09-12'
insert into Table1(ID,RecordDate,Status,StatusDate) select 2,'2019-11-30','Nope','2020-09-11'
insert into Table1(ID,RecordDate,Status,StatusDate) select 3,'2019-10-24','Soup','2020-09-12'

SELECT  
    xx.id
  , STRING_AGG(CONVERT(NVARCHAR(max), xx.status), ';') AS [Status Sequence] 
from 
  ( select distinct B.id,b.status FROM Table1 B INNER JOIN Table1  A ON B.Id = A.Id ) xx
group by 
    xx.id ;

image

Now i have the order by working

; with cte as 
(
  select distinct top 100 B.id,b.status FROM Table1 B INNER JOIN Table1  A ON B.Id = A.Id order by b.status desc
) 
SELECT  
    xx.id
  , STRING_AGG(CONVERT(NVARCHAR(max), xx.status), ';') AS [Status Sequence] 
from 
  cte xx 
group by 
    xx.id ;

image

9

Apologies I completely forgot the documentation!
I have used the function in the past but it completely slipped my mind that instead of a normal ORDER BY, something else is needed!

Thanks