Assuming that you would like to transform data between below forms:
Original data | | Expected output |
---|
FormId | FieldName | FieldValue
6 | epFname | Jack Doll
6 | epEmail | jdoll@mail.com
6 | epFname | Ron Young
6 | epEmail | ryoung@mail.com | |
FormId | FullName | EmailAddress
6 | Jack Doll | jdoll@mail.com
6 | Ron Young | ryoung@mail.com |
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
FROM (
SELECT FormId, ROW_NUMBER() OVER w AS 'Index', FieldValue AS 'FullName'
FROM YourTable
WHERE FieldName ='epFname' AND FormId = 6
WINDOW w AS (ORDER BY FormId)
) AS t1 INNER JOIN (
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;
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", "jdoll@mail.com"})
dt.Rows.Add(New Object(){6, "epFname", "Ron Young"})
dt.Rows.Add(New Object(){6, "epEmail", "ryoung@mail.com"})
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")))
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 jdoll@mail.com
6 1 Ron Young ryoung@mail.com
More:
MySQL :: MySQL 8.0 Reference Manual :: 12.19.1 Window Function Descriptions[
^]