Geeks With Blogs
Malisa Ncube - .NET Delights .NET Development ideas and things

In should say that the first time I looked lambda and linq, making pivot tables was not the main thing I thought about. After helping a few people including one at the msdn (http://is.gd/oG8q) social site, I realized that I also needed to use the lambda in my own EMR project.

The problem was to have the lab data of a patient transformed into a pivot table and some of the data summarized by averaging or counting.

I had the data in the table in the following format.

 

PatientID

TestNo

TestName

TestVar

Result

ResultUnit

CollectedDate

1

1010

CBC2

LYABS

2.0

10^3 /uL

1/6/2008 12:00:00 AM

1

1010

CBC2

MCHC

33.4

g/dL

1/6/200812:00:00 AM

 

I then had to write a linq expression to produce the following results.

Patient

TestNo

CollectedDate

MCHC

LYABS

TotalTests

1

1010

9/30/2008 12:00:00 AM

32.8

1.9

19

1

1010

1/6/2008 12:00:00 AM

33.4

2.0

21

1

1010

3/3/2008 12:00:00 AM

null

null

1

1

1010

7/2/2008 12:00:00 AM

33.0

2.2

21

           

The linq was as follows.

var labResults = from lab in CoreLabResults
                 where lab.Patient == 8
                 group lab by new { lab.Patient, lab.TestNo, lab.CollectedDate }
                     into labtests
                     select new
                     {
                         labtests.Key.Patient,
                         labtests.Key.TestNo,
                         labtests.Key.CollectedDate,
                         MCHC = labtests.Where(lab => lab.TestVar == "MCHC").FirstOrDefault().Result,
                         LYABS = labtests.Where(lab => lab.TestVar == "LYABS").FirstOrDefault().Result,
                         TotalTests = labtests.Count()
                     }

The lambda expression for the above was be as follows

var labResults = CoreLabResults
    .Where(lab => lab.Patient == 8)
    .GroupBy(lab =>
            new
            {
                Patient = lab.Patient,
                AccessionNo = lab.AccessionNo,
                CollectedDate = lab.CollectedDate
            }
            )
    .Select(labtests =>
            new
            {
                Patient = labtests.Key.Patient,
                AccessionNo = labtests.Key.TestNo,
                CollectedDate = labtests.Key.CollectedDate,
                MCHC = labtests.Where(lab => (lab.TestVar == "MCHC")).FirstOrDefault().Result,
                LYABS = labtests.Where(lab => (lab.TestVar == "LYABS")).FirstOrDefault().Result,
                TotalTests = labtests.Count()
            }
);

I still need to find out how I can replace the column names like “MCHC” by computed ones. I may probably need to use the expression trees to dynamically create lambda expressions that have variable number of columns. E.g. in financial statements - I would not want to hardcode column names of each financial period year/quarter.

Apologies – syntax highlighting using Live writer is hell.

Posted on Tuesday, April 21, 2009 8:55 PM | Back to top


Comments on this post: Using lambda or LINQ for pivot tables

# re: Using lambda or LINQ for pivot tables
Requesting Gravatar...
Very good!
Thanks you.
Left by nmducit on Oct 06, 2009 8:41 PM

# re: Using lambda or LINQ for pivot tables
Requesting Gravatar...
You welcome, i'm glad it was of help
Left by Malisa Ncube on Oct 07, 2009 1:49 AM

# re: Using lambda or LINQ for pivot tables
Requesting Gravatar...
Did you ever figure out how to set up the variable number of columns?
Left by Ryon on Oct 20, 2009 5:29 AM

# re: Using lambda or LINQ for pivot tables
Requesting Gravatar...
Actually i did not look into much detail about that, but i think it should be possible using expression trees or some dynamic linq framework like NLinq.
Left by Malisa Ncube on Oct 20, 2009 5:32 AM

# re: Using lambda or LINQ for pivot tables
Requesting Gravatar...
Hi Malisa,

I just left a comment on http://social.msdn.microsoft.com/Forums/en/csharplanguage/thread/e3512ff5-cae6-4ba9-95a9-d187797b8b75?prof=required thanking you for your post, but it was actually THIS one that helped me figure out what I needed (after the fun I had trying to translate the syntax to VB, lol).

Thanks,

Yann
Left by Yann on Jan 28, 2010 4:23 PM

# re: Using lambda or LINQ for pivot tables
Requesting Gravatar...
this is static solution, but i want to use pivot table solution variable data item,Like in future if i have to add one or more column which might be my other data items
Left by Aakash on Sep 03, 2010 12:21 AM

# re: Using lambda or LINQ for pivot tables
Requesting Gravatar...
Thanks for this little tutorial. I am curious if you know of a way to 'un-pivot' data. So basically, if you wanted to reverse the process using LINQ.

T-SQL has a UNPIVOT operator, but i can't seem to find how to do it in LINQ.
Left by varosh on Oct 15, 2010 12:06 PM

# re: Using lambda or LINQ for pivot tables
Requesting Gravatar...
Have you tried building the columns dynamically using DynamicLinq?
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
http://stackoverflow.com/questions/1465700/system-linq-dynamic-select-new-into-a-listt-or-any-other-enumerable
Left by Llarian on Mar 25, 2011 1:06 PM

# re: Using lambda or LINQ for pivot tables
Requesting Gravatar...
muy buen aporte, me ha sorprendido! .. Gracias desde Mexico!!
Left by veronica on Dec 17, 2012 2:05 PM

Your comment:
 (will show your gravatar)


Copyright © Malisa L. Ncube | Powered by: GeeksWithBlogs.net | Join free