Suppose I have the following table
The LINQ query should pivot as follows
name testID Value
John Glu 4.3
John Ket 2.1
John Cl- 3.2
Mary Glu 4.3
Mary Na+ 4.2
Mary Cl- 6.9
Pete Na+ N/A
Pete Cl- 3.1
Name Glu Ket Na+ Cl-
John 4.3 2.1 NUL 3.2
Mary 4.3 NUL 4.2 6`.9
Pete NUL NUL N/A 3.1
Whilst I understand how this can be done with SQL
, but I have no clue how to do this with LINQ
(especially if
field containing plus, underscore or minus signs)
First of all, I was wondering if this is even possible do this in LINQ
(with plus or minus signs) with VB.NET
, if so can someone point us to the right direction please?
Many thanks
EDITED
The linq I wrote kind of half works without the Na+ or Cl- part:
from f in (from r in results group r by r.name into group ) _
let a as object = _
(From e in f.Group where e.testID= "Glu" select e.Value).max _
let b as object = _
(From e in f.Group where e.testID= "Ket" select e.Value).max _
let c as object = _
(From e in f.Group where e.testID= "Na+" select e.Value).max _
let d as object = _
(From e in f.Group where e.testID= "Cl-" select e.Value).max _
select f.name, Glu = a, Ket = b, [Na+] = c, [Cl-] = d
substituting
[Na+] = c , [Cl-] = dwith
Na = c , Cl = dit will work as expected, but is there a way have plus or minus sign display on the column header?