how to perform a sequential pivot in an MS ACCESS database in dapper with VB.NET

Question

I'm trying to perform a sequential pivot in an MS ACCESS database in dapper with VB.NET

I have the code below, but this is still wrong.

is there any other method please guide me

Thanks

Public Class Form2
    Dim tps As New Tableproductservice()

    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGridView1.DataSource = tps.Loaddata("A")
    End Sub
End Class

Public Class Tableproductservice
    Private connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\transposerowtocolumnsqlmsaccessvbnet.accdb;Persist Security Info=False"

    Public Function Loaddata(CodeProduct As String) As IEnumerable(Of Tableproduct)
        Dim sql = <sql>
                              TRANSFORM Sum(Tableproduct.[Qty]) AS SumOfQty
        SELECT Tableproduct.Codeproduct AS [CodeProduct], Tableproduct.Colour AS [Colour], Sum(Tableproduct.Qty) AS [Total]
        FROM Tableproduct INNER JOIN SizeProduct ON Tableproduct.Size = SizeProduct.Size
        WHERE Tableproduct.Codeproduct = 'A'
        GROUP BY Tableproduct.Codeproduct, Tableproduct.Colour
        PIVOT SizeProduct.Size;
                          </sql>.Value
        Using _conn = New OleDbConnection(connectionString)
            Return _conn.Query(Of Tableproduct)(sql, New With {Key .CodeProduct = CodeProduct}).ToList()
        End Using
    End Function
End Class

Public Class Tableproduct
    Public Property CodeProduct() As String
    Public Property Colour() As String
    Public Property Size() As String
    Public Property Qty() As Integer
    Public Property Total() As Integer
End Class

Public Class SizeProduct
    Public Property Size() As String
    Public Property Sequence() As Integer
End Class

But the result in datagridview does not pivot is there anything wrong please guide me below screenshot of results in datagridview

but with the same sql in vb.net results in MS ACCESS database have been pivoted but not yet sequentially. below screenshot of results in MS-ACCESS database

Sample Data :

Table TableProduct

CodeProduct	Colour	Size	Qty
A	White	S	15
A	Black	M	20
A	White	L	10
B	Blue	S	20
B	White	XL	15

Table Sizeproduct

Sizeproduct	Sequence
S	1
M	2
L	3
XL	4

Desired Result :

Where For Codeproduct : A

CodeProduct	Colour	S	M	L	TOTAL
A	Black		20		20
A	White	15		10	25

Where For Codeproduct : B

CodeProduct	Colour	S	XL	TOTAL
B	Blue	20		20
B	White		15	15

Result in datagridview answer from @OlivierJacot-Descombes

Answer 1

Well, your Loaddata method always returns Tableproduct objects and this is the only thing your DataGridView sees. You must create a class which represents your pivoted result and return it from the Loaddata method.

Public Class PivotedProduct
    Public Property CodeProduct() As String
    Public Property Colour() As String

    Public Property S() As Integer
    Public Property M() As Integer
    Public Property L() As Integer
    Public Property XL() As Integer

    Public Property Total() As Integer
End Class

But this will of course always display all the size columns in the grid. I am not sure what you mean by "sequential". If you mean grouped by product, then this cannot be achieved with the DataGridView. But some third-party grids are available that support grouping.

If you want to display only the relevant size columns per grouped product, then this will be difficult or impossible with a grid control. But it would be possible to solve this problem with a reporting tool and some logic.

Here is an example loading the data in the grid and hiding empty columns (but only for one product at a time). I created a service loading some fake test data for testing:

Public Class PivotedProductService
    Public Function LoadData(CodeProduct As String) As IEnumerable(Of PivotedProduct)
        Return New List(Of PivotedProduct) From {
            New PivotedProduct With {.CodeProduct = "B", .Colour = "Blue", .S = 20, .Total = 20},
            New PivotedProduct With {.CodeProduct = "B", .Colour = "White", .XL = 15, .Total = 15}
        }
    End Function
End Class

My form code looks like this:

Public Class Form1
    Dim pps As New PivotedProductService

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGridView1.DataSource = pps.LoadData("B")
        HideEmptySizeColumns()
    End Sub

    Private Sub HideEmptySizeColumns()
        For i As Integer = 2 To 5
            Dim allZeros = True
            For Each row As DataGridViewRow In DataGridView1.Rows
                If row.Cells(i).Value <> 0 Then
                    allZeros = False
                    Exit For
                End If
            Next
            If allZeros Then
                DataGridView1.Columns(i).Visible = False
            End If
        Next
    End Sub
End Class

The result looks like this:

---

Using LINQ you can simplify the code for hiding the columns like this

Private Sub HideEmptySizeColumns()
    For i As Integer = 2 To 5
        Dim allZeros = Not (From row In DataGridView1.Rows
                            Where row.Cells(i).Value <> 0).Any()
        If allZeros Then
            DataGridView1.Columns(i).Visible = False
        End If
    Next
End Sub