SQL code to join to, and sum data in, a table referenced by comma delimited keys

Question

I have two tables like this:

InfoTable
-------------------
AVNR    Substation       ColumnTitle     S6_name      AVNRString
-------------------------------------------------------------------
1129     AHWAZ-1        T3+T4              MW          1129,1134
1130     AHWAZ-1        T3+T4              MX          1130,1135
1134     AHWAZ-1        T3+T4              MW          1129,1134
1135     AHWAZ-1        T3+T4              MX          1130,1135
8906     SHOMAL         T9                 MW             8906
8907     SHOMAL         T9                 MX             8907

and

DataTable
------------------------------------------------
Pdate           Ptime     AVNR        Wert
------------------------------------------------
03-13-2017      01:00     1129         12.65
03-13-2017      02:00     1129         25.65
03-13-2017      03:00     1129         102.05
03-12-2017      01:00     1129         12.65
03-14-2017      01:00     1129         12.65
03-16-2017      05:00     1129         12.65    
03-13-2017      01:00     1134         12.65
03-13-2017      02:00     1134         25.65
03-13-2017      03:00     1130         102.05
03-12-2017      01:00     1135         11.15
03-14-2017      01:00     1130         10.00
03-16-2017      01:00     8906         24.5
03-13-2017      01:00     8906         23.5
03-16-2017      02:00     8907         22.5
03-13-2017      01:00     8907         21.5
03-16-2017      05:00     8906         12.5
03-13-2017      02:00     8907         20.5

I want to have this result

 ResultTable
-------------------
Substation    ColumnTitle     S6_name   Pdate       pTime   Sum_of_Wert     
-------------------------------------------------------------------
 AHWAZ-1        T3+T4          MW      03-13-2017    01:00   25.3  (sum of Wert for 1129,1134  for that date and time)
 AHWAZ-1        T3+T4          MW      03-13-2017    02:00   51.3  (sum of Wert for 1129,1134  for that date and time)
 AHWAZ-1        T3+T4          MW      03-13-2017    03:00    xxx
 AHWAZ-1        T3+T4          MX      03-14-2017    01:00    xxx
 SHOMAL         T9             MW      03-13-2017    01:00    xxx
 SHOMAL         T9             MW      03-13-2017    02:00    xxx

......

i.e. I want to the sum value for Substations that are distinguished in AvrString for each date and time .

Is there by any chance I could do this in SQL ? I not, perhaps in Linq? I have programmed in visual studio, but this is slow. I want to know if there is any idea for that.

I am programming in c# in visual studio with sql database

Just to add that you should use `GROUP BY` on the column `AVNR` — , May 30 '18 at 05:49
what have you tried so far? can you create a http://sqlfiddle.com — Nerdroid, May 30 '18 at 05:52
Visit w3school for basic SQL server join and grouping concept. for current problem refer [http://sqlfiddle.com/#!18/82353/2][1] — Rajat Jaiswal, May 30 '18 at 05:53

StuartLC · Answer 1 · 2018-05-30T06:22:16.830

The issue here is that whomever designed the InfoTable didn't abide by the rules of proper table normalization, and embedding strings like 1129,1134 indicating relationships between keys in other rows makes life very difficult.

If you are using a modern version of SQL Server - 2016 or later - your bacon is saved with the STRING_SPLIT function.

This will allow you to re-normalize the table (i.e. split out rows for each AVNR in AVNRString). You can then cast the split AVNR back to INT in order to join to this and apply it in a grouping, i.e.

WITH normalizeInfoTable AS
(
   SELECT it.Substation, it.ColumnTitle, it.S6_name, CAST(cs.Value as INT) as AVNR
   FROM InfoTable it
   CROSS APPLY STRING_SPLIT (it.AVNRString, ',') cs
)
SELECT it.Substation, it.ColumnTitle, it.S6_name, dt.Pdate, dt.pTime, SUM(dt.Wert) 
  FROM normalizeInfoTable it
  INNER JOIN DataTable dt
  ON it.AVNR = dt.AVNR
  GROUP BY it.Substation, it.ColumnTitle, it.S6_name, dt.Pdate, dt.pTime;

SqlFiddle up here

Notes

The Info 'pairings' are duplicated in your table, i.e. the effect of

AVNR    Substation       ColumnTitle     S6_name      AVNRString
1129     AHWAZ-1        T3+T4              MW          1129,1134
1134     AHWAZ-1        T3+T4              MW          1129,1134

produces 4 rows as each of 1129 and 1134 will be duplicated twice each. If this isn't what's needed, then you can eliminate duplicates by adding a DISTINCT in the CTE, i.e.

SELECT DISTINCT it.Substation, it.ColumnTitle, it.S6_name, CAST(cs.Value as INT) as AVNR
...

Also note that proper normalization is still preferred, since the AVNR relationships can be enforced with foreign keys, and performance improved with an index on this column. Applying STRING_SPLIT function has performance penalties.

This is good answer thought STRING_SPLIT function only available since version SQL SERVER 2016. If any body use version before that then Split UDF need to be written first. — dotnetstep, May 30 '18 at 06:44

Wei Lin · Answer 2 · 2018-05-30T07:06:59.750

@StuartLC 's answer is awesome

here's anthor way for you "use distinct and like":

with CTE AS (
  select distinct [Substation], [ColumnTitle],[S6_name], [AVNRString]
  from InfoTable T1
)
select T2.Substation,T2.ColumnTitle, T2.S6_name,T1.Pdate,T1.pTime,sum(T1.Wert) Sum_of_Wert
from DataTable T1
left join InfoTable T2 on  
  T2.AVNRString = T1.AVNR
  or T2.AVNRString like '%,'+T1.AVNR+'%'
  or T2.AVNRString like '%'+T1.AVNR+',%'
group by T2.Substation,T2.ColumnTitle, T2.S6_name,T1.Pdate,T1.pTime
order by Sum_of_Wert

SQL Fiddle DEMO LINK

ps: thanks @dotnetstep

In my opinion like is not good option if it does not explicit separator. In your case if T1 table has value 1129 ( AVNR) and AVNRString contains 1129,1134 and some other row contains 11290,1134 then also it will satisfy condition.

i try this condition script to avoid it.

   T2.AVNRString = T1.AVNR
      or T2.AVNRString like '%,'+T1.AVNR+'%'
      or T2.AVNRString like '%'+T1.AVNR+',%'

In my opinion like is not good option if it does not explicit separator. In your case if T1 table has value 1129 ( AVNR) and AVNRString contains 1129,1134 and some other row contains 11290,1134 then also it will satisfy condition. — dotnetstep, May 30 '18 at 06:47

score 0 · Answer 3 · answered May 31 '18 at 22:41

here a query with STRING_SPLIT

SELECT it.Substation,it.ColumnTitle,it.S6_name,dt.Pdate,dt.pTime, SUM(dt.Wert) AS Sum_of_Wert
FROM InfoTable it
CROSS APPLY (SELECT value FROM STRING_SPLIT(it.AVNRString, ',') ) x
CROSS APPLY (SELECT * FROM DataTable  WHERE AVNR = CAST(x.value as INT) ) dt
GROUP BY it.Substation,it.ColumnTitle,it.S6_name,dt.Pdate,dt.pTime

SQL Fiffle link http://sqlfiddle.com/#!18/e1ed2d/1

SQL code to join to, and sum data in, a table referenced by comma delimited keys

3 Answers3