Code to Swap Rows and Columns (Pivot) the Results of a Linq Query

Posted on 2012-08-29
Last Modified: 2012-09-23
Good evening, I am faced with having to do the following (and preferably not having to manually code something and loop through the results repeatedly).  We have a table with about 30 fields in it which we run a Linq query against to pull a set of results that might be a something like the following (subset of data below):

ID     DATE           BP           HEIGHT     WEIGHT     PULSE
1      1/1/12     120/70           6'0           210             80
2      1/8/12     120/70           6'0           212             76
3      1/15/12   120/70           6'0           214             60

and I need to turn it into this:

DATE             1/1/12               1/8/12            1/15/12
BP                 120/70              120/70             120/70
HEIGHT            6'0                     6'0                   6'0
WEIGHT          210                    212                 214  
PULSE              80                      76                     60

Now I know this is probably the simplest thing in the world, but it has been a long time since programming classes for me :)  so I am looking for some help.

There are actually about 30 fields in the source table and an average of about 20 results per query we will need to format in this manner.  It is basically all records tracking as you may have guessed some data about a persons health status over a period of time.

Thanks in advance!
Question by:bas2754
    LVL 18

    Expert Comment

    this is the answer very similar for your  needs  and data columns:
    Using lambda or LINQ for pivot tables
    LVL 9

    Author Comment

    That really isn't at all what I am trying to do.  I need to dynamically create each column based on the data that is in the date field.  I do not need to sum or count any information, just display it in the format shown above.

    In all the examples so far that I have found, the fields have needed to be hard coded into the LINQ query - including the example in the link above.

    I basically need to be able to take the column field names from the original table and create a row from each of those column field names with the proper value for that field inserted into the date column of the newly created table.

    I am about to just give up and write a very messy set of loops for this, but was hoping for a more elegant LINQ solution.  

    I am looking for a code snipped example that might be thought out more than just a link to here is something like what you want to do.  Please post code that would turn that first table into the second if it is doable.  

    Thank You again in advance.
    LVL 9

    Accepted Solution

    Ended up coding this in a series for for next loops.
    LVL 9

    Author Closing Comment

    Seems that either no one else had a better idea or that no one had a more specific example.  The link provided by the one responder was one I had seen already, but as stated I am looking for a way to not have to code any field names into the query.

    Unfortunately Links are not answers and since no one else commented or offered solutions and I needed to get this done, I ended up doing it the old fashioned way.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
    Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now