SQL - delimiting + matching

Question

I have a table with the follow columns: reportid, reportname, startdate, consolidated

Reports which are consolidated do not have a start date.

What I need to do is to find the earliest start date within the subreports and set it as the start date

For example

report  reportname  startdate  consolidated
1       ABC         2019/1/1   1
2       DEF                    3,4
3       GHI         2019/4/1   3
4       JKF         2019/5/1   4

The report may be consolidated from any number of reports (ie. report 10 may consist of 11,12,13 while report 20 may consist of only 21 and 22)

Output required

report  reportname  startdate  consolidated
1       ABC         2019/1/1   1
2       DEF         2019/4/1   3,4
3       GHI         2019/4/1   3
4       JKF         2019/5/1   4

I can only think of pulling each number and looping through the entire list, comparing each date that is picked up as I go. However, this list is very very long which doesn't make it very feasible.

Thanks in advance!

Unfortunately, I do not have the authority to adjust the database where these tables are concerned.

Do you think about make table with relations instead `consolidated` column? — Leszek Mazur, Jun 04 '20 at 10:13
Read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** — Zohar Peled, Jun 04 '20 at 10:17

score 1 · Accepted Answer · answered Jun 04 '20 at 10:56

You need to fix your data model. Storing multiple values in a string is wrong. Storing numbers in a string is wrong.

Sometimes, we are stuck with other peoples really, really bad decisions. You can do what you want, but it is more complicated than necessary:

select t.*
from (select t.*, min(startdate) over (partition by s.value) as imputed_startdate,
             row_number() over (partition by report order by report) as seqnum
      from t outer apply
           string_split(t.consolidated, ',') s 
     ) t
where seqnum = 1;

Here is a db<>fiddle.

SQL - delimiting + matching

1 Answers1