how i can pivot data in sql with c#

Question

how i can pivot data in sql with c#

amir 20

I have a table in my SQL Server database named 'UserDetails,' where the data is stored row-wise. Now, I need to generate a report where this data is returned in a column-wise format. For instance, the 'UserDetails' table has columns like 'firstname,' 'lastname,' and 'phone' as keys, storing information row-wise. How can I retrieve this data in a column-wise format from the database for reporting purposes?

Anonymous

Hi @amir , Welcome to Microsoft Q&A,

You can use data structures in C# such as List or Array to store query results in rows. Here is a sample code that demonstrates how to execute a query and store the results row by row into a List<string[]>:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

class Program
{
     static void Main()
     {
         string connectionString = "YourConnectionString"; // Replace with your database connection string

         List<string[]> rows = new List<string[]>();

         using (SqlConnection connection = new SqlConnection(connectionString))
         {
             string sqlQuery = "SELECT firstname, lastname, phone FROM UserDetails";

             using (SqlCommand command = new SqlCommand(sqlQuery, connection))
             {
                 connection.Open();

                 using (SqlDataReader reader = command.ExecuteReader())
                 {
                     //Read each row of data and add it to the list
                     while (reader.Read())
                     {
                         string firstname = reader.GetString(0);
                         string lastname = reader.GetString(1);
                         string phone = reader.GetString(2);

                         string[] row = { firstname, lastname, phone };
                         rows.Add(row);
                     }
                 }
             }
         }

         // Now you can further process the data in the list, such as outputting to a file or database
         // For example, write data to the console
         foreach (string[] row in rows)
         {
             Console.WriteLine(string.Join("\t", row));
         }
     }
}

Best Regards,

Jiale

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Accepted answer

0 additional answers

Your answer

Anonymous

2024-02-07T06:10:17.5133333+00:00

Hi @amir , Welcome to Microsoft Q&A,

You can use data structures in C# such as List or Array to store query results in rows. Here is a sample code that demonstrates how to execute a query and store the results row by row into a List<string[]>:

using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; class Program { static void Main() { string connectionString = "YourConnectionString"; // Replace with your database connection string List<string[]> rows = new List<string[]>(); using (SqlConnection connection = new SqlConnection(connectionString)) { string sqlQuery = "SELECT firstname, lastname, phone FROM UserDetails"; using (SqlCommand command = new SqlCommand(sqlQuery, connection)) { connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { //Read each row of data and add it to the list while (reader.Read()) { string firstname = reader.GetString(0); string lastname = reader.GetString(1); string phone = reader.GetString(2); string[] row = { firstname, lastname, phone }; rows.Add(row); } } } } // Now you can further process the data in the list, such as outputting to a file or database // For example, write data to the console foreach (string[] row in rows) { Console.WriteLine(string.Join("\t", row)); } } }

Best Regards,

Jiale

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 1

Thanks for posting your question in the Microsoft Q&A forum.

Here's an example query that demonstrates how to achieve this:

var result = await _userDetails.Table
    .GroupBy(x => x.userId)
    .Select(x => new
    {
        UserId = x.Key,
        FirstName = x.FirstOrDefault(item => item.Key == "FirstName").Value ?? "",
        LastName = x.FirstOrDefault(item => item.Key == "LastName").Value ?? "",
        Phone = x.FirstOrDefault(item => item.Key == "Phone").Value ?? ""
    })
    .ToDictionaryAsync(x => x.UserId, x => new
    {
        FirstName = x.FirstName,
        LastName = x.LastName,
        Phone = x.Phone
    });

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful

Share via

how i can pivot data in sql with c#

0 additional answers

Your answer