Converting Rows to Columns without loop

Question

Hey,

I have a situation where i have data like following

User1   Address1    HomeAddress     Address1Value
User1   Address2    WorkAddress     Address2Value
User1   Phone1      HomePhone       Phone1Value
User1   Phone2      WorkPhone       Phone2Value
User2   Address1    HomeAddress     Address1Value
User2   Address2    WorkAddress     Address2Value
User2   Phone1      HomePhone       Phone1Value
User2   Phone2      WorkPhone       Phone2Value
User3   Address1    HomeAddress     Address1Value
User3   Address2    WorkAddress     Address2Value
User3   Phone1      HomePhone       Phone1Value
User3   Phone2      WorkPhone       Phone2Value

Now i have to display the above data as following in a gridview.

**Users    HomeAddress      WorkAddress      HomePhone      WorkPhone**
  User1    Address1Value    Address2Value    Phone1Value    Phone2Value
  User2    Address1Value    Address2Value    Phone1Value    Phone2Value
  User3    Address1Value    Address2Value    Phone1Value    Phone2Value

One option i have is that i loops through the dataset and convert 4 rows of data into columns. and then bind the data. but that does not look like an efficient way to do since i have plenty of data.

Is there any efficient way to do this?

I am using LINQ to SQL for this. I have a Sql Server view that returns the result of 4 rows per user.

Answer 1

If you are already transporting "plenty of data" over the network from the database to the app, then the last step of pivoting it in memory isn't likely to be a big problem.

IMO, either pivot it "at source", or just pivot it locally and then see if it is a bottleneck. You could also write some funky binding model, but I doubt it is worth it. A final option is to use "virtual mode" (if the list supports it), and perform the translation during the lookup (ideally using a dictionary-based row lookup - perhaps a precomputed Lookup<,>).

Answer 2

Not to digress too far from Marc's answer, I highly recommend doing it on SQL Server. Pivoting is generally a much faster operation there (SQL server is optimized to do this kind of stuff after all).

Additionally, it's code you don't have to write (meaning the pivot code, since SQL provides you with the operation, as opposed to writing it in .NET on your own), which is an important point here, IMO.