0

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

Edited by krunal1986: edited post

2
Contributors
1
Reply
4
Views
4 Years
Discussion Span
Last Post by deceptikon
0

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();
        }
    }
}
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.