i Want the following conversion for the table

by

A solution to your problem would be to query your database table to get all records using the method of your choice.

Once queried, you can delete all records.

Run a loop on all records and use the string.Split(',') method on the PHYSICIAN_ID field to get a string[] containing all physician IDs for that record.

Once obtained, you can run an insert statement for each physician ID in each record to insert all the records as required.

Here’s an example of what I mean in terms of C# code (documented by comments):

var table = new DataTable(); // Create table for storing query results

var connection = new SqlConnection("connectionString"); // Declare connection to server
connection.Open(); // Attempt to connect to specified server

var command = new SqlCommand("SELECT * FROM Table", connection); // Declare query for server
var adapter = new SqlDataAdapter(command); // Declare a data adapter for the query
adapter.Fill(table); // Execute the query

var deleteCommand = new SqlCommand("DELETE FROM Table", connection); // Declare delete statement
deleteCommand.ExecuteNonQuery(); // Execute delete statement

var strBuilder = new StringBuilder(); // StringBuilder for creating SQL statements
foreach (DataRow record in table.Rows) // For every row in the table
{
    string[] physicians = record["PHYSICIAN_ID"].ToString().Split(','); // Turn the CSV format into a string[]
    foreach (string physician in physicians) // For all physicians for that patient
    {
        strBuilder.Append("INSERT INTO Table (PATIENT_ID, PHYSICIAN_ID) VALUES ("
            + record["PATIENT_ID"].ToString() + ", " + physician + ");");
        // Append the StringBuilder with a statement to insert the new record
        // for every physician as applicable
    }
}

var insertCommand = new SqlCommand(strBuilder.ToString(), connection); // Create insert statements from the StringBuilder
insertCommand.ExecuteNonQuery(); // Execute insert statements

connection.Close(); // Close connection to the server

More Related Contents:

  1. Taking lot of time to read from SQL Server using c#
  2. System.IndexOutOfRangeException: Cannot find table 0
  3. I want to insert a record in DB and then need to return a row
  4. Read data from SqlDataReader
  5. How do I clean SqlDependency from SQL Server memory?
  6. Adding multiple parameterized variables to a database in c#
  7. Format of the initialization string does not conform to specification starting at index 0
  8. Encrypt connection string in app.config
  9. export to Excel from a list with EPPLUS
  10. How to connect to database from Unity
  11. What is the right way to populate a DropDownList from a database?
  12. Entity Framework 6 GUID as primary key: Cannot insert the value NULL into column ‘Id’, table ‘FileStore’; column does not allow nulls
  13. SQL WHERE clause matching values with trailing spaces
  14. Entity Framework/SQL2008 – How to Automatically Update LastModified fields for Entities?
  15. How to pass User Defined Table Type as Stored Procedured parameter in C#
  16. SqlDataSourceEnumerator.Instance.GetDataSources() does not locate local SQL server 2008 instance
  17. SqlParameter does not allows Table name – other options without sql injection attack?
  18. Entity Framework/Linq to SQL: Skip & Take
  19. A dependent property in a ReferentialConstraint is mapped to a store-generated column
  20. ‘datetime2’ error when using entity framework in VS 2010 .net 4.0
  21. How to create “embedded” SQL 2008 database file if it doesn’t exist?
  22. Syncing SQL Server 2008 Databases over HTTP using WCF & Sync Framework
  23. How to load image from SQL Server into picture box?
  24. get date part only from datetime value using entity framework
  25. Calling SQL Defined function in C#
  26. When is it better to write “ad hoc sql” vs stored procedures [duplicate]
  27. Passing datatable to a stored procedure
  28. Add third party dll reference in ssis script component
  29. 3 Digit currency code to currency symbol
  30. Does FileStream.Dispose close the file immediately?

Leave a Comment