How to convert rows to columns in sql server 2008

IsaiSelvan S

8y1.2k

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

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manish Kumar8y ago
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 S8y ago
Hi Sudipta,

Thanks for your reply. i need the query in backend (sql server 2008) not in front end (c#).
sudipta sanyal8y ago
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 < returnTable.Rows.Count; i++)
{
for (int j = 1; j < returnTable.Columns.Count; j++)
{
returnTable.Rows[i][j] =
table.Rows[j - 1][returnTable.Rows[i][0].ToString()].ToString();
}
}
return returnTable;
}
IsaiSelvan S8y ago
Hi Ramesh,

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

Can you please give a sample query for this.
Dharmraj Thakur8y ago
If you dont have bulky data then you can write column wise queries with where conditions.
Ramesh Palanivel8y ago
Hi IsaiSelvan,

Have you visited this link ?

http://www.aspdotnet-suresh.com/2012/09/how-to-convert-rows-to-columns-in-sql.html
IsaiSelvan S8y ago
Thanks Ramesh. But i need without using pivot and unpivot functionality. the sql server 2008 not supported for pivot and unpivot functionality.
Ramesh Palanivel8y ago
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/