将字符串中不同键值对上的数据分组 [英] Group data on different key-value pairs in a string

问题描述

我有一个表LOG，其中包含一个字段NOTES.表LOG也包含字段NrofItems.这是在Azure SQL上. NOTES是一个字符串，其中包含用分号分隔的键/值对.键值对的顺序是随机的.键是已知的.

I have a table LOG that contains a field NOTES. Table LOG also contains a field NrofItems. This is on Azure SQL. NOTES is a string that contains key-value pairs separated by semicolons. The order of the key-value pairs is random. The keys are known.

三个记录的示例:

NOTES | NrofItems    
"customer=customer1;code=blablabla;application=SomeApplication"  | 23
"code=adfadfadf;customer=customer99;application=AlsoApplication" | 33
"code=xyzxyzxyz;application=AlsoApplication;customer=customer1"  | 13
"code=blablabla;customer=customer1;application=SomeApplication"  |  2

我需要对每个应用程序的每个客户的每个客户的NrofItems的值求和:

I need to sum the value of NrofItems per customer per application per... like this:

customer1  | blablabla | SomeApplication | 25
customer1  | xyzxyzxyz | AlsoApplication | 13
customer99 | adfadfadf | AlsoApplication | 33

我希望能够使用一个或多个键值对进行分组.

I would like to be able to use one or more of the key-value pairs to make groupings.

我确实知道如何进行分组，但是如何分组?

I do know how to to it for one grouping but how for more?

请参阅此URL，以了解如何针对一个分组进行操作:部分分组字符串

See this URL to see how to do it for one grouping: Group By on part of string

推荐答案

嗯.为此，我认为分别提取客户和应用程序是一种便捷的方法:

Hmmm. For this, I'm thinking that extracting the customer and application separately is a convenient way to go:

select c.customer, a.application, sum(nrofitems)
from t outer  apply
     (select top (1) stuff(s.value, 1, 10, '') as customer
      from string_split(t.notes, ';') s
      where s.value like 'customer=%'
     ) c outer apply
     (select top (1) stuff(s.value, 1, 12, '') as application
      from string_split(t.notes, ';') s
      where s.value like 'application=%'
     ) a
group by c.customer, a.application;

这里是db 小提琴.

Here is a db<>fiddle.

这篇关于将字符串中不同键值对上的数据分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！