How do you aggregate in SQL based on a portion of a string

Question

I have a row in a table that looks like this

    Time                        TenantID  CallingService     PolicyList  PolicyInstanceList 
    2019-11-23 07:30:30.0000000 ########  S1                 p1, p2      pi1,pi2,pi3,pi4
    2019-11-23 07:30:31.0000000 ########  S1                 p1, p2      pi1,pi2,pi3,pi4
    2019-11-23 07:30:32.0000000 ########  S1                 p1, p2      pi1,pi2,pi3,pi4    
    2019-11-23 07:30:33.0000000 ########  S1                 p1, p2      pi1,pi2,pi3,pi4    
    2019-11-23 07:30:34.0000000 ########  S1                 p1, p2      pi1,pi2,pi3,pi4    
    2019-11-23 07:30:35.0000000 ########  S1                 p1, p2      pi1,pi2,pi3,pi4    
    2019-11-23 07:30:36.0000000 ########  S1                 p1, p2      pi1,pi2,pi3,pi4    
    2019-11-23 07:30:37.0000000 ########  S1                 p1, p2      pi1,pi2,pi3,pi4
    2019-11-23 07:30:38.0000000 ########  S1                 p1, p2      pi1,pi2,pi3,pi4

I have aggregated it based on the TenantID so that it looks like this

Time                        TADCount    TenantID
2019-11-23 00:00:00.0000    8           ########

However, I need to add more granularity to my aggregation so I can do it based on multiple keys. For example,

Time                        TADCount    TenantID  CallingService  PolicyList  PolicyInstanceList 
2019-11-23 00:00:00.0000    1           ########  S1              p2          pi3

Here is the aggregation statement I have been using for reference to achieve the 2nd code block.

  SELECT DATEADD(DAY, DATEDIFF(DAY, 0, [Time]), 0) AS Time
      ,Count([TenantId]) AS TADCount, [TenantId]
      --,Count([PolicyList]) AS PolicyListCount, [PolicyList]
  FROM [dbo].[acms_data] 
  GROUP by DATEADD(DAY, DATEDIFF(DAY, 0, [Time]), 0),[TenantId]

This question isn't asked very well. Can you provide a complete description of the data you have, an example of the data you want and any code that you've tried so far. — DCR, Jul 08 '19 at 18:57
Please show the 8 rows for that TenantID. What is to happen for comma separated lists (e.g. S1,S2 or pi1, pi2,pi3,pi4). Please edit the result above to change p3 to be pi3. How is "more granularity" to be communicated to the SQL? By column name only, or also by value? CallingService or CallingService=S1 ? — donPablo, Jul 08 '19 at 19:04
The 1st portion of code is exactly what table looks like. The 2nd is what I already have tried and done and the 3rd block is what I would like to achieve. The 4th is what I have already tried. — , Jul 08 '19 at 19:04
@donPablo I have edited the 1st block to give more clarity I hope it helps — , Jul 08 '19 at 19:09
You shouldn't be storing comma separated values in a single column to begin with. Do you have a chance to fix your broken data model and properly normalize it? — , Jul 08 '19 at 19:11
Oh I did not know that it should not be formatted as such. I will look at it now. — , Jul 08 '19 at 19:12

score 0 · Answer 1 · answered Jul 08 '19 at 19:38

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, [Time]), 0) AS Time
      , Count([TenantId]) AS TADCount
      , [TenantId]
      , CallingService
      , right(PolicyList,2) as PolicyList
      , substring(PolicyInstanceList, 9, 3) as PolicyInstanceList
  FROM [dbo].[acms_data] 
  GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, [Time]), 0)
         , [TenantId]
         , CallingService
         , right(PolicyList,2)
         , substring(PolicyInstanceList, 9, 3);

score 0 · Accepted Answer · answered Jul 08 '19 at 20:12

0

If you're using sql server 2016 or above, you have access to the string_split table valued function. In the example below, I'm applying it to the policyList, as opposed to the policyInstanceList, but you can change the logic as you need.

select      time = ap.day,
            acms.tenantId,
            policyList = ltrim(sp.value),
            policyListCount = count(policyList)
from        acms_data acms
cross apply string_split(acms.policyList, ',') sp
cross apply (select day = convert(date, acms.time)) ap
group by    ap.day, acms.tenantId, sp.value

If you have a version under 2016, then you'll have to build your own string split function. There are many questions on stack overflow that talk about creating your own such function. See here for one example.

However, once you find yourself storing csv lists inside columns, that is a sign that your tables are starting to denormalize. So if you can restructure some of your tables so that the relation between tennant and policyList (or policyInstanceList) is captured in another table, that would be more ideal.

answered Jul 08 '19 at 20:12

pwilcox

5,542
1
19
31

Thanks for answering. What does ap.day mean just wondering. I have not seen that before – Jul 08 '19 at 21:02
In the second cross apply statement, named 'ap', I create a calculated column called 'day' which simply converts your column of type 'time' to type 'date'. This approach allows you to write the calculation once, and reference it where needed, instead of re-writing the calculation wherever you use it. If you desire, you can change the logic of 'day' to 'dateadd(day...' as you had in your example, but if it works for you, I'd go with the simpler logic. – pwilcox Jul 08 '19 at 22:38
is this algorithm able to be used in larger granularity such as week month etc – Jul 09 '19 at 18:20
Yes. You may want to look into the 'datepart' function and use it in the logic for 'ap'. – pwilcox Jul 09 '19 at 20:30

How do you aggregate in SQL based on a portion of a string

2 Answers2