DB table

The table has a TestResult column, which stores the results of the participants (in%) for some indicators:

 DECLARE @TestResults TABLE(ParticipCode INT, Elements VARCHAR(100)) INSERT INTO @TestResults VALUES (1, '0,50;0,20;0,30') INSERT INTO @TestResults VALUES (2, '0,60;0,24;0,32')

enter image description here

REQUIRED

It is necessary to obtain an average value for each indicator, ie:

enter image description here

And there is no difference in what form they will be presented in the result set -

in rows

enter image description here

or columns

enter image description here

How to get this? After it turns out to make a script, I want to make on its basis a stored procedure that would accept such an array and calculate the averages.

Attempts

1. I work in SQL Server 2016 and naturally I decided to resort to the STRING_SPLIT innovation. But unfortunately my knowledge and attempts were enough only for the ability to parse one line:

 DECLARE @str VARCHAR(MAX) = (SELECT TOP 1 Elements FROM @TestResults); SELECT * FROM STRING_SPLIT(@str, ';')

enter image description here

2. Also on the forum I found a solution that is based on the built-in function PARSENAME . It does not fit, because This feature will stop working with more than 4 indicators. I have in example 3, but in reality there are more of them.

  • 2
    It should be understood that "change the structure so as to normalize the data" - do not offer? - Akina
  • He will not be able to use STRING_SPLIT, because he has positional indicators, and when divided this positionality will be safely lost ... - Akina
  • @Akina, yes - I know that I have it denormalized. It is necessary to solve the problem with such input parameters. - Adam
  • @Akina So you need to get an average, I think the order is not important - Mike
  • one
    @Akina But it is guaranteed to confuse him with repeated elements. The guaranteed solution is one - in the recursive CTE, cut one element from the beginning of the line in parallel, numbering them and giving the next line to the next level of recursion. But with a meager set of string functions ms sql, I am too lazy to write it - Mike

1 answer 1

Unfortunately, I do not have SQL Server 2016 at hand, to check my script, I will try to suggest an option, if I make a mistake somewhere, correct me.

 select t.rn, avg(t.value) from ( select tr.ParticipCode , row_number() over (partition by tr.ParticipCode order by tr.ParticipCode) as rn , cast(ss.value as float) as value from @TestResults tr cross apply STRING_SPLIT(tr.Elements, ';') as ss ) as t group by t.rn
  • one
    I think I can work something like this, but in all cases I would not vouch for the correct result. no one said row_number would number the numbers in the correct order - Mike
  • @Mike, the order is really not guaranteed, but it must still be maintained - after all, this is a function, not a scan of physical pages, in which the server can optimize the returned set depending on the physical storage location. In any case, you can replace the string_split function with a samopisnuyu, where the set will be numbered - such functions were actively used when there was no implementation from Microsoft. - minamoto
  • The code does not work - The varchar operand data type is not valid for the avg operator . I tried to correct: avg(convert(float, value)) and still does not work: The column "t.rn" is not allowed in the select list, because it is not contained in the aggregate function or in the GROUP BY clause. - Adam
  • @adamshakhabov, at the end add a group by rn. In the answer I will correct now. - minamoto
  • In continuation of the discussion I will add an interesting article about the performance study of this function - based on the conclusions of the article, the performance significantly exceeds the previously existing row splitting options. And in one of the future releases it is planned to add the return of the exact position of the element in the set. Article address: sqlperformance.com/2016/03/sql-server-2016/string-split - minamoto