Cross table VB.NET & SQL Server & Linq

Question

I have a table like this:

    MAName  feldtext
    ------------------
    karl    fieldtext1
    karl    fieldtext2
    karl    fieldtext1
    karl    fieldtext3
    karl    fieldtext4
    karl    fieldtext2
    karl    fieldtext5
    karl    fieldtext3
    karl    fieldtext3
    susi    fieldtext1
    susi    fieldtext4
    john    fieldtext2
    john    fieldtext5
    john    fieldtext5

and I need:

    MAName  fieldtext1  fieldtext2  fieldtext3  fieldtext4  fieldtext5   FehlerJeMA
    karl    2           2           3           1           1             9
    susi    1           0           0           1           0             2
    john    0           1           0           0           2             3

The columns fieldtext can go from fieldtext1 to fieldtextn, it's dynamic, depending on query.

I was looking here for solutions and found, so my approach:

Dim dt2 As New DataTable
Dim nn As Integer = 0
Dim Zeile As DataRow

dt2.Columns.Add("MAName")
' fieldtext distinct
Dim query2 = (From dr In (From d In newTable2.AsEnumerable Select New  With {.feldtext1 = d("feldtext")}) Select dr.feldtext1 Distinct)

For Each Feldtext In query2
    dt2.Columns.Add(Feldtext)            
Next

column = New DataColumn()
column.DataType = System.Type.GetType("System.Int32")
column.ColumnName = "FehlerJeMA"
dt2.Columns.Add(column)

' MAName distinct
Dim query3 = (From dr In (From d In newTable2.AsEnumerable Select New With {.MAName2 = d("MAName")}) Select dr.MAName2.ToString.ToLower Distinct)

For Each Mitarbeiter In query3
    Zeile = dt2.NewRow()
    Zeile(0) = Mitarbeiter.ToString.ToLower
    MA2 = Mitarbeiter.ToString.ToLower

    nn = 1

    For Each colName2 In query2

        Fehler2 = colName2
        Dim AnzahlFehler As String = (From row In newTable2.Rows Select row Where row("MAName").ToString.ToLower = MA2 And row("feldtext") = Fehler2).Count
        If AnzahlFehler = 0 Then
            AnzahlFehler = ""
        End If
        Zeile(nn) = AnzahlFehler

        nn += 1

        If AnzahlFehler <> "" Then
            FehlerJeMA += CInt(AnzahlFehler)
        End If
    Next

    Zeile(nn) = FehlerJeMA

    dt2.Rows.Add(Zeile)
Next

This works, but is very slow...

It could be the case that in my table has more than 10.000 rows...

So my question is: what is fastest approach to get the result?

Is it some kind of cross table with linq? Other approaches?

If you have a large number of rows in the table I would recommend to use SQL Server solution. You can Google for "SQL Server dynamic Pivot". SO already has a few answers: http://stackoverflow.com/questions/25443598/sql-server-dynamic-pivot and http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Alex, Jun 18 '16 at 11:16

score 0 · Answer 1 · answered Jun 18 '16 at 11:33

0

In C# you will be able to use the code, try to translate it for your problem:

var pivotData = data.GroupBy(x => new {x.MAName, x.feldtext}, (key, group) => new { MAName = key.Column1, feldtext = key.Column2, count = group.Count()  });

answered Jun 18 '16 at 11:33

Marek Woźniak

1,766
16
34

I'm not familiar with C# so i used converter from http://codeconverter.sharpdevelop.net and converter from http://converter.telerik.com/ both tell me:Dim pivotData = data.GroupBy(Function(x) New From { _ x.MAName, _ x.feldtext _ }, Function(key, group) New With { _ Key .MAName = key.Column1, _ Key .feldtext = key.Column2, _ Key .count = group.Count() _ }) but whe i paste code in VB i got error on first opening bracket after NEW FROM. can you tell me why? – frank123 Jun 18 '16 at 12:16
@frank123 i can't but the algorithm is similar to: group by MAName then by feldtext, get keys and count elements in group. that's all :) – Marek Woźniak Jun 18 '16 at 12:37

Cross table VB.NET & SQL Server & Linq

1 Answers1