How to convert rows to columns in sql server 2008

Question

Hi Team, please find the below table format. I need the output for the below format. I'm trying to retrieve output format but only one column able to bind. I'm working in Sql server 2008 R2. I'm working last 2 days but i can't to bind it.I need the without using pivot and unpivot function. Table: ====== AAA AA+ AA AA- A 6.05 7.15 9.5 8.5 B 3.02 4.58 6.25 7.25 C 2.25 1.02 0 6 Output: ======= No A B C AAA 6.05 3.02 2.25 AA+ 4.05 3.04 1.02 AA 3.02 4.05 0 AA- 2.03 3.01 6

sudipta sanyal · Answer

Try with this DataTable table = new DataTable(); table.Columns.Add("Class"); table.Columns.Add("AAA"); table.Columns.Add("AA+"); table.Columns.Add("AA"); table.Columns.Add("AA--"); table.Rows.Add("A", "6.05", "7.15", "9.5", "8.5"); table.Rows.Add("B", "3.02", "4.58", "6.25", "7.25"); table.Rows.Add("C", "2.25", "1.02", "0", " 6"); DataTable dtReturn = GetInversedDataTable(table, "Class"); ----------------------------------------------------------------- public static DataTable GetInversedDataTable(DataTable table, string columnX, params string[] columnsToIgnore) { //Create a DataTable to Return DataTable returnTable = new DataTable(); if (columnX == "") columnX = table.Columns[0].ColumnName; //Add a Column at the beginning of the table returnTable.Columns.Add(columnX); //Read all DISTINCT values from columnX Column in the provided DataTale List columnXValues = new List (); //Creates list of columns to ignore List listColumnsToIgnore = new List (); if (columnsToIgnore.Length > 0) listColumnsToIgnore.AddRange(columnsToIgnore); if (!listColumnsToIgnore.Contains(columnX)) listColumnsToIgnore.Add(columnX); foreach (DataRow dr in table.Rows) { string columnXTemp = dr[columnX].ToString(); //Verify if the value was already listed if (!columnXValues.Contains(columnXTemp)) { //if the value id different from others provided, add to the list of //values and creates a new Column with its value. columnXValues.Add(columnXTemp); returnTable.Columns.Add(columnXTemp); } else { //Throw exception for a repeated value throw new Exception("The inversion used must have " + "unique values for column " + columnX); } } //Add a line for each column of the DataTable foreach (DataColumn dc in table.Columns) { if (!columnXValues.Contains(dc.ColumnName) && !listColumnsToIgnore.Contains(dc.ColumnName)) { DataRow dr = returnTable.NewRow(); dr[0] = dc.ColumnName; returnTable.Rows.Add(dr); } } //Complete the datatable with the values for (int i = 0; i { for (int j = 1; j { returnTable.Rows[i][j] = table.Rows[j - 1][returnTable.Rows[i][0].ToString()].ToString(); } } return returnTable; }

Ramesh Palanivel · Answer

Hi IsaiSelvan, You can do this using sql Pivot option. Please find the below URL for your reference, https://www.c-sharpcorner.com/article/convert-rows-to-columns-using-dynamic-pivot-in-sql-server/

Manish Kumar · Answer

Who told you sql server 2008 does not support pivot and unpivot. Chek below link. https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

IsaiSelvan S · Answer

Hi Sudipta, Thanks for your reply. i need the query in backend (sql server 2008) not in front end (c#).

IsaiSelvan S · Answer

Hi Ramesh, I'm tryied the same way but its not working. can you please give the sample query.

IsaiSelvan S · Answer

Hi Thakur, Can you please give a sample query for this.

Dharmraj Thakur · Answer

If you dont have bulky data then you can write column wise queries with where conditions.

Ramesh Palanivel · Answer

Hi IsaiSelvan, Have you visited this link ? http://www.aspdotnet-suresh.com/2012/09/how-to-convert-rows-to-columns-in-sql.html

IsaiSelvan S · Answer

Thanks Ramesh. But i need without using pivot and unpivot functionality. the sql server 2008 not supported for pivot and unpivot functionality.