convert rows to clumns

[Nihir]

I have a grid in dataset as following


UserPatientStatusID RelevantPeriod TotalCount ProviderSpecialtyServiceName
a OUT November2008 1 Not Available
a ER September2008 1986 Not Available
a ER April2009 1837 Not Available
a ER December2009 2367 Not Available
a ER August2008 1908 Not Available
b ER February2009 1598 Not Available
b ER January2009 1972 Not Available
b ER June2009 2087 Not Available
b ER December2008 2012 Not Available
b ER November2009 2148 Not Available
b ER November2008 2030 Not Available
c ER October2009 2240 Not Available
c ER September2009 2029 Not Available
c ER October2008 2079 Not Available
c ER July2010 1714 Not Available
c ER August2009 2115 Not Available
c OUT July2010 2 Not Available
c ER March2010 1755 Not Available
c ER May2010 1759 Not Available
c ER July2009 2063 Not Available
c OUT April2009 1 Not Available
c OUT December2008 1 Not Available
c ER March2009 1931 Not Available
c REC August2009 1 Not Available


I know that there can be 24 relevant periods for each user. There may or may not be all 24 present for each user.

Now all that 24 periods I have in an array in C#. I know what these periods will be. They change so I cannot hardcode it. It has to be dynamic.


I want to create 24 columns of that rows for relevant periods and display TotalCount in that and get all other data accordingly in c#.


Please advise me or show me some sample code.


Thanks in advance

 

[jmeckley]

sounds like a pivot table. there are a couple ways to solve this. the technical details will depend on your specific needs. conceptually it would look something like this:
1. get the raw data
2. get a list of the pivot column headers. in this case a distinct list of values from the raw data.
3. create a new object to contain the pivoted data (either a dataset, or a POCO [plain old compiled object])
4. loop through the raw data, creating the pivoted data.
5. return the pivoted data.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[jbenson001]

If you are returning the data from a sql server stored procedure, you can do all the pivoting there. Look into the PIVOT operator.

 

[Nihir]

I cannot use sql server for this.

@jmickey: can you please give me some sample code..

 

[jbenson001]

You should be able to pivot directly in any major DB appliccation. What DB are you using?

 

[jmeckley]

one approach using data tables.

public DataTable Pivot(DataTable source)
{
   var variableHeaders = source
                    .Rows
                    .AsEnumerable()
                    .Select(row => row["column_to_pivot"].ToString())
                    .Distinct()
                    .OrderBy(value => value);
   var headers = new[]{"fixed_column_1"}.Concat(variableHeaders);
   var pivot = new DataTable();
   foreach(DataRow row in source.Rows)
   {
       var found = pivot.Select("[fixed_column_1]=" + row["the_fixed_column"]).FirstOrDefault();
       if(found == null)
       {
          found = pivot.NewRow();
          found["fixed_column_1"] = row["the_fixed_column"];
          pivot.AddRow(found);
       }
       found[row["column_to_pivot"].ToString()] = row["column_with_pivot_value"];
   }
   return pivot;
}

this is off the top of my head. I'll leave the syntax errors as an exercise to the user. As I mentioned previously, you could also use POCOs instead of datatables.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[Nihir]

this does help me a bit. But I dont have new created columns.

var found = pivot.Select("[fixed_column_1]=" + row["the_fixed_column"]).FirstOrDefault();
fails as my pivot table is empty.
so how will i have my all columns..

 

[Nihir]

@jmeckley: Using your method I can do this and it works fine. Can you by any chance please help me use Linq doing exact samething as below

var variableHeaders = data.Tables[0].AsEnumerable()
.Select(row => row["RelevantPeriod"].ToString())
.Distinct();

var pivot = new DataTable();
foreach (string column in variableHeaders)
{
data.Tables[0].Columns.Add(column);
}
pivot = data.Tables[0].Clone();
pivot.TableName = "PivotTable";
DataRow pivorRow = pivot.NewRow();
for (int rowcount = 0; rowcount < data.Tables[0].Rows.Count; rowcount++)
{
DataRow currentRow = data.Tables[0].Rows[rowcount];

if (pivorRow["AttendingProviderID"].ToString() != currentRow["AttendingProviderID"].ToString())
{
//if (pivorRow["PatientStatusID"].ToString() != currentRow["PatientStatusID"].ToString())
{
pivorRow = pivot.NewRow();
pivot.Rows.Add(pivorRow);
pivorRow["AttendingProviderID"] = currentRow["AttendingProviderID"].ToString();
}
}
pivorRow["ProviderName"] = currentRow["ProviderName"].ToString();
pivorRow["PatientStatusID"] = currentRow["PatientStatusID"].ToString();
pivorRow["ProviderSpecialtyServiceName"] = currentRow["ProviderSpecialtyServiceName"].ToString();

foreach (string column in variableHeaders)
{
if (column == currentRow["RelevantPeriod"].ToString())
{
pivorRow[column] = currentRow["TotalCount"];
}
}

}

 

[jmeckley]

some of this code is already using the linq extensions. it's just not the syntax sugar. and... if it works why change it?

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[Nihir]

well, the query takes a bit long to execute, so I have been told by my manager to try LINQ

 

[jmeckley]

LOL, that won't change a thing. and if you're talking about linq2sql... well that could decrease preformance if you don't know what you're doing.

if it takes too long you need to
1. determine where the bottleneck is. profilers likes Ants and dotTrace can assist with this.
2. once you know where the bottleneck is you can resolve that pain point.
3. repeat until the results are acceptable.

latency is usually related to remote calls (other servers, sql statements, file IO, etc). ways to reduce the problem:
1. batch operations when possible. ado.net allows this within idbcommand and idbdatareader interfaces.
2. design efficient queries. most times selecting all the data in 1 query isn't the most efficient query plan. indexing and reducing the number of joins will also increase throughput.
4. managing the size of the results returned. if you are returning 100's or 1000's of rows then you may need to change your approach. the more you can aggregate the data at the source the better. also you don't need all the records loaded into memory before executing. taking advantage of IEnumerable and the yield keyword you can process a single record at a time keeping the memory foot print low and processing 1000's of records at a time.
5. depending on the business needs and technical skills of the developers you can push the work "off-line". that is calculate the data into the final result before the user requests it. when the user requests the data the "real work" has already been completed, they are just viewing the results.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259