Click here to Skip to main content
15,662,774 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a Table with the following properties:

Columns are: FormId | FieldName | FieldValue |
Rows are:

6 | epFname | Jack Doll
6 | epEmail |
6 | epFname | Ron Young
6 | epEmail |

Im trying to create a list view that retrieves the data into columns preset in the ListView, but i cannot seem to get the correct query to pull the data so it lists correctly in the listview as follows:

ListView Columns: FormId | Full Name | Email Address
ListView Data: 6 | Jack Doll |
6 | Ron Young |

this is my current code using the MySQL.Data connector

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sqlQuery As String = "SELECT (FormId), (FieldName), (FieldValue) FROM jos_rsform_submission_values WHERE FormId='6'"

        Dim sqlAdapter As New MySqlDataAdapter
        Dim sqlCommand As New MySqlCommand
        Dim Table As New DataTable
        Dim i As Integer

        With (sqlCommand)
            .CommandText = sqlQuery
            .Connection = sConnection
        End With
        With sqlAdapter
            .SelectCommand = sqlCommand
        End With
        For i = 0 To Table.Rows.Count - 1
            With lvList
                With .Items(.Items.Count - 1).SubItems
                End With
            End With
    End Sub
Updated 1-Mar-18 1:58am
fjdiewornncalwe 19-Jan-12 12:56pm    
Could you tell us what is going wrong? We're not psychic.
Member 8580885 19-Jan-12 13:33pm    
Im not sure how to explain it any better, if the table column headers were Name, Email, FormId there wouldnt be an issue, but the table structure is not like that

under the column header FieldName is the Name and Email fields, and Under the FieldValue column header is the values for Name and Email.

The query string is what i need fixed. this isnt right but may give you an idea:

SELECT FormId, FieldName, FieldValue FROM jos_rsform_submission_values WHERE FieldName='Name' AND FieldName='Email'

And gets the FieldValues from those Table Rows
A_Griffin 28-Feb-18 12:42pm    
Really I think you should re-think your database design. It's not very good as is, and you will have difficulty making sensible queries on it.
Maciej Los 1-Mar-18 5:10am    
Are you sure that FormId is the same for Jack Doll and Ron Young?
Are there any other fields in your table?

1 solution

Assuming that you would like to transform data between below forms:

Original data Expected output
FormId | FieldName | FieldValue
6      | epFname   | Jack Doll
6      | epEmail   |
6      | epFname   | Ron Young
6      | epEmail   |
FormId | FullName  | EmailAddress
6      | Jack Doll |
6      | Ron Young |

You need to pivot data. What this means? You have to "convert" unique data of FieldName volumn into columns. In pseudo code:
[FullName] = If [FieldName]="epFname" Return [FieldValue] Else Nothig
[EmailAddres] = If [FieldName]="epEmail" Return [FieldValue] Else Nothig

In T-SQL you can achieve that by using CASE WHEN statement[^] + GROUP BY[^].

MySQL doesn't know PIVOT and UNPIVOT[^] operators as is well known in MS SQL. In MySQL you have to create view. See:
sql - MySQL - Rows to Columns - Stack Overflow[^]
Pivot a table in MySQL – Tutorial - Stratos Blog[^]

BUT! You won't be able to achieve that, because FormId is the same for both names and email addresses.

To workaround it, use below trick:

SELECT t1.FormId, t1.[Index], t1.FullName, t2.EmailAddress
    SELECT FormId, ROW_NUMBER() OVER w AS 'Index', FieldValue AS 'FullName'
    FROM YourTable
    WHERE FieldName ='epFname' AND FormId = 6
    SELECT FormId, ROW_NUMBER() OVER w AS 'Index', FieldValue AS 'EmailAddress'
    FROM YourTable
    WHERE FieldName ='epEmail' AND FormId = 6
    WINDOW w AS (ORDER BY FormId)) AS t2 ON t1.FormId = t2.FormId AND t1.Index =  t2.Index;

'original data becomes form database
'i need to create sample data, so...
Dim dt As DataTable = New DataTable()
dt.Columns.Add(New DataColumn("FormId", Type.GetType("System.Int32")))
dt.Columns.Add(New DataColumn("FieldName", Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("FieldValue", Type.GetType("System.String")))
dt.Rows.Add(New Object(){6, "epFname", "Jack Doll"})
dt.Rows.Add(New Object(){6, "epEmail", ""})
dt.Rows.Add(New Object(){6, "epFname", "Ron Young"})
dt.Rows.Add(New Object(){6, "epEmail", ""})

'create another datatable to store pivoted data
Dim finalDt As DataTable = New DataTable()
finaldt.Columns.Add(New DataColumn("FormId", Type.GetType("System.Int32")))
finaldt.Columns.Add(New DataColumn("Index", Type.GetType("System.Int32")))
finaldt.Columns.Add(New DataColumn("FullName", Type.GetType("System.String")))
finaldt.Columns.Add(New DataColumn("EmailAddress", Type.GetType("System.String")))

'get data from original datatable
Dim data = (From a In dt.AsEnumerable() _
	.Where(Function(x) x.Field(Of String)("FieldName")="epFname") _
	.Select(Function(x, i) New With _
		{ _
			.FormId = x.Field(Of Integer)("FormId"), _
			.Index = i, _
			.FullName = x.Field(Of String)("FieldValue") _
		}).ToList() Join b In  dt.AsEnumerable() _
		.Where(Function(x) x.Field(Of String)("FieldName")="epEmail") _
		.Select(Function(x, i) New With _
			{ _
				.FormId = x.Field(Of Integer)("FormId"), _
				.Index = i, _
				.EmailAddress = x.Field(Of String)("FieldValue") _
			}).ToList() On a.FormId Equals b.FormId And a.Index Equals b.Index) _
		.Select(Function(joined) finaldt.LoadDataRow(New Object() _
			{ _
				joined.a.FormId, _
				joined.a.Index, _
				joined.a.FullName, _
				joined.b.EmailAddress _
			}, False)).ToList()

finaldt = data.CopyToDataTable()

Now finaldt is storing pivoted data:
FormId Index FullName   EmailAddress
6      0     Jack Doll 
6      1     Ron Young 

MySQL :: MySQL 8.0 Reference Manual :: 12.19.1 Window Function Descriptions[^]
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900