0

I'm looking for a way in SQL Server 2016 to coalesce or concatenate rows into a comma or semi colon separated string while also counting the number of like phrases where date is greater than selected date.

Example Table shown below.

PPDate      Column 1
2017/09/30  NULL
2017/10/14  1 Outside Hire; 2 Internal Reassignment
2017/10/28  NULL
2017/11/11  1 Part-Time
2017/11/25  1 Participant Returns; 1 Outside Hire
2017/12/09  NULL
2017/12/23  NULL
2018/01/06  2 Reassignment-External; 1 Temp Reassignment
2018/01/20  NULL
2018/02/03  1 Part-Time
2018/02/17  1 Outside Hire

How do I coalesce or concatenate column 1 where PPDate is greater than 2017/10/14 for example and also count occurrences of the semi colon delimited phrases?

Resulting string should be:

2 Part-Time; 1 Participant Returns; 2 Outside Hire; 2 Reassignment-External; 1 Temp Reassignment
James Z
  • 12,209
  • 10
  • 24
  • 44
Ropeh
  • 43
  • 6
  • Does this answer your question? [Comma separated results in SQL](https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql) – Thom A Oct 27 '20 at 14:25
  • Does this answer your question? [string_agg for sql server pre 2017](https://stackoverflow.com/questions/49361088/string-agg-for-sql-server-pre-2017) – Thom A Oct 27 '20 at 14:26
  • If you post code, XML or data samples, highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it – marc_s Oct 27 '20 at 14:31
  • Thanks Larnu. The link suggested with the code does not give the expected results as I tried a similar code. – Ropeh Oct 27 '20 at 15:50

0 Answers0