rearrange values in sql server table

[krunal1986]

hi,

i have records in table like this:

id v1 v2 v3
1 587 21 759
2 230 4 495

now i want ouput like below:

id v1 v2
1 587 21
2 587 759
3 230 4
4 230 495

i am stuck here don't know what will be query for this.

p help me

thanks

[deceptikon]

With only the given information, I'd favor breaking down the row and building a table in C# rather than trying to transform the rows in the query. It looks like all you're doing is splitting each row into two rows with the first id duplicated and assigning either the 2nd or 3rd id:

using System;
using System.Data;
using System.Data.SqlClient;

public class Program {
    public static void Main() {
        string connectionString = "Data Source=localhost;Initial Catalog=Test;Integrated Security=SSPI;";
        string query = "select * from TestTable";
        var table = new DataTable();

        table.Columns.Add("id", typeof(int));
        table.Columns.Add("id1", typeof(int));
        table.Columns.Add("id2", typeof(int));

        using (var connection = new SqlConnection(connectionString)) {
            connection.Open();

            using (var command = new SqlCommand(query, connection)) {
                using (var reader = command.ExecuteReader()) {
                    int pk_seq = 1;

                    while (reader.Read()) {
                        var row = table.NewRow();

                        row["id"] = pk_seq++;
                        row["id1"] = reader["id1"];
                        row["id2"] = reader["id2"];

                        table.Rows.Add(row);
                        row = table.NewRow();

                        row["id"] = pk_seq++;
                        row["id1"] = reader["id1"];
                        row["id2"] = reader["id3"];

                        table.Rows.Add(row);
                    }
                }
            }
        }

        foreach (DataColumn col in table.Columns)
            Console.Write("{0,-5}", col.ColumnName);

        Console.WriteLine();

        foreach (DataRow row in table.Rows) {
            foreach (var item in row.ItemArray)
                Console.Write("{0,-5}", item);

            Console.WriteLine();
        }
    }
}