Teguh Eko Budiarto

I needed a pivot table to display a report to the user in my company, so I tried googling the best and practical way to do that. At first, I was interested in using MS Office components and just pass the data to the component and refresh the template so it will display the data I need. But, the thing is I will need the component also to be installed in the server, and this is not a really high priority apps to put the server in risk to not working for some time if there is anything wrong with the installation or the components it self. So, I decided using the data table manipulation and I found a good example on how to do that in weblogs of sql team here.

However, there is small modification I need to do since the function written there only accommodate single key column. After modified it, I was planning to put the update in comments of the page so I can contribute somehow. Unfortunately the comments is already closed, so I decided to put it here. Find it below. This pivot function is placed in a web service which is going to return a dataset containing the pivot table. After receiving it in the web app, I format the datagrid so it will display as the user need. Yay! Alhamdulillah. Hope it will be useful for other people who need it.

///

/// Transform a table to a pivot table based on the given information.
/// 

/// 
/// The table which is going to be transformed to pivot table. The table should be sorted by the key colums, starting from most left part.
/// 
/// 
/// Array of String which contains the columns in dataValues that is going to be used as a keyColumn for display
/// 
///

/// The Column name of the dataValues which is going to be displayed as pivot table's column
///
///

/// The Column name of the dataValues which is going to be used to fill the value for the pivot table's column
///
/// 
public static DataTable Pivot( DataTable dataValues, string[] keyColumn, string pivotNameColumn, string pivotValueColumn)
{
	try
	{
		DataTable tmp = new DataTable();
		DataRow r;
		string[] LastKey = new string[keyColumn.Length];
		int i,keyColumnIndex, pValIndex, pNameIndex;
		string s;
		bool FirstRow = true;
		bool keyChanged = false;

		// Add non-pivot columns to the data table:
		pValIndex = dataValues.Columns.IndexOf(pivotValueColumn);
		pNameIndex = dataValues.Columns.IndexOf(pivotNameColumn);

		for (i = 0; i <= dataValues.Columns.Count - 1; i++)
			if (i != pValIndex && i != pNameIndex)
			{
				tmp.Columns.Add(dataValues.Columns[i].ColumnName.ToString(), dataValues.Columns[i].DataType);
			}
		r = tmp.NewRow();

		// now, fill up the table with the data:
		foreach (DataRow row in dataValues.Rows)
		{
			// see if we need to start a new row
			keyColumnIndex = 0;
			keyChanged = false;
			if (!FirstRow)
			{
				while (!keyChanged && keyColumnIndex < keyColumn.Length)
				{
					if (row[keyColumn[keyColumnIndex]].ToString() != LastKey[keyColumnIndex])
					{
						keyChanged = true;
					}
					keyColumnIndex++;
				}
			}
			else
			{
				for (keyColumnIndex = 0;keyColumnIndex<= dataValues.Columns.Count - 3; i++)
				{
					r[i] = row[tmp.Columns[i].ColumnName];
				}
				FirstRow = false;

				for (keyColumnIndex = 0;keyColumnIndex 0)
			{
				if (!tmp.Columns.Contains(s) && s != null)
				{
					tmp.Columns.Add(s, dataValues.Columns[pValIndex].DataType);
				}
				r[s] = row[pValIndex];
			}
		}
		// add that final row to the datatable:
		tmp.Rows.Add(r);

		// and that's it!
		return tmp;
	}
	catch (Exception)
	{
		return null;
	}
}

Sometimes you want to get a date format to a locale which is different than your current SQL Server machine. For example, I have a Japanese SQL Server Machine, but I want to return the date format in US English settings.

What I did was to use the command: SET LANGUAGE [locale]

http://msdn.microsoft.com/en-us/library/ms174398.aspx