Complex Group by in C#

Question

I have a set of rows as below in DB

ID,CompName,identifier,DesId,SubId,SRO,PDiam
-----------------------------------------------
1,resistor,R1,10,20,30,45
2,resistor,R2,10,20,40,50
3,capacitor,C1,10,20,33,5
4,Inductor,I1,10,20,22,32
1,Inductor,I2,10,20,42,52

I am trying to group them and siplay based on their Identifier as follows. Assume column separator is pipe(|)

Ids|CompName|SRO|PDiam
-------------------------------
R1,R2|resistor|30|45  //Since there are two resistors, 2 rows foreach SRO and Pdiam Pair
R1,R2|resistor|40|50  
C1|Capacitor|33|5 //One Row for Capacitor as only 1 component in DB and one pair of SRO and Pdiam Pair
I1,I2|Inductor|22|32 //2 Rows for 2 capacitors and 1 row for each pair of SRO and PDiam
I1,I2|Inductor|42|52

I know we should use group by but not sure how to proceed it in an efficient way. The only way I could think of is iterate through the result of Group by ComponentName and Identifier but that seems too complex logic to me.

Any better way to achieve this?

Aren't you only trying to group by CompName and not identifier? — ProgrammingLlama, May 31 '19 at 06:11
I'm trying both but I am having multiple for each which makes code difficult to understand — Programmerzzz, May 31 '19 at 06:13
Why are you showing both R1 and R2 as resistor|30|45 when that only applies to R1? What is ID, is it supposed to be unique? — iakobski, May 31 '19 at 07:11

Intergalactic.Coconut · Answer 1 · 2019-05-31T07:20:33.433

0

You could make a join of your table with the aggregated version of your table. The aggregated table would group by CompName and use string aggregation function on Identifier.

SELECT G.Ids, T.compname, T.SRO, T.PDiam FROM
components T LEFT JOIN
(SELECT compname, STRING_AGG(identifier, ',') ids FROM components GROUP BY compname) G
ON T.compname = G.compname

(not tested)

String aggregation is however problematic in T-SQL before SqlServer 2017, so it may be difficult to do. See this post

If you need to do this in EF, the approach would be the same. Not sure if you can 'export' the string aggregation function to EF. (should be possible).

For small data in EF you can use something like this

edited May 31 '19 at 07:20

answered May 31 '19 at 06:57

Intergalactic.Coconut

11
3

I think he is looking for a C# solution – Peter Smith May 31 '19 at 07:17
1

@Peter Smith I know, not sure what DB access he uses though. It is always an option to create a view inside the database using all the DB power and accessing that view as a table for results – Intergalactic.Coconut May 31 '19 at 07:24
I'm using SQL server 2012 – Programmerzzz May 31 '19 at 11:44
@Programmerzzz how do you access the DB from .NET code? – Intergalactic.Coconut May 31 '19 at 13:50
Yes I do but only once at beginning of function t read and once at the end to write reduce db roundtrips..using entity framework – Programmerzzz May 31 '19 at 13:52

Complex Group by in C#

1 Answers1