Malisa Ncube - .NET Delights

.NET Development ideas and things
posts - 41 , comments - 86 , trackbacks - 0

My Links

News



I LOVE DataObject.NET
http://xceed.com
http://www.sharpcrafters.com/
http://www.telerik.com

Get this blog as a slideshow!
Powered by feedmap.net

Twitter












Tag Cloud

Archives

Post Categories

Using lambda or LINQ for pivot tables

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.

Print | posted on Tuesday, April 21, 2009 8:55 PM |

Feedback

Gravatar

# re: Using lambda or LINQ for pivot tables

Very good!
Thanks you.
10/6/2009 8:41 PM | nmducit
Gravatar

# re: Using lambda or LINQ for pivot tables

You welcome, i'm glad it was of help
10/7/2009 1:49 AM | Malisa Ncube
Gravatar

# re: Using lambda or LINQ for pivot tables

Did you ever figure out how to set up the variable number of columns?
10/20/2009 5:29 AM | Ryon
Gravatar

# re: Using lambda or LINQ for pivot tables

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.
10/20/2009 5:32 AM | Malisa Ncube
Gravatar

# re: Using lambda or LINQ for pivot tables

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
1/28/2010 4:23 PM | Yann
Gravatar

# re: Using lambda or LINQ for pivot tables

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
9/3/2010 12:21 AM | Aakash
Gravatar

# re: Using lambda or LINQ for pivot tables

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.
10/15/2010 12:06 PM | varosh
Gravatar

# re: Using lambda or LINQ for pivot tables

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
3/25/2011 1:06 PM | Llarian
Gravatar

# re: Using lambda or LINQ for pivot tables

muy buen aporte, me ha sorprendido! .. Gracias desde Mexico!!
12/17/2012 2:05 PM | veronica
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 
 

Powered by: