Hi smile,

There is no way without defining the columns to display pivot result in Crystal Report.

You need to define the as many number of columns (by assuming max columns from pivot query) in the dataset to display the columns.

For this example i have added 10 columns in dataset.

Using the two article i have created the example.

Basic Crystal Report Tutorial with example in Windows Forms (WinForms) Application using C# and VB.Net

Create Crystal Report with dynamic columns in ASP.Net using C# and VB.Net

For demonstration purpose i have taken two Button to fetch different number of column to display the report.

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

DataSet Column

Report Design

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Linq
Imports System.Windows.Forms
Imports CrystalDecisions.CrystalReports.Engine

Code

C#

private void BindReport(string query)
{
    ReportDocument crystalReport = new ReportDocument();
    crystalReportViewer1.DisplayGroupTree = false;
    crystalReport.Load(Application.StartupPath.Replace("bin\\Debug", "") + "DynamicReport.rpt");
    DynamicDataSet dsCustomers = GetData(query, crystalReport);
    crystalReport.SetDataSource(dsCustomers);
    this.crystalReportViewer1.ReportSource = crystalReport;
    this.crystalReportViewer1.RefreshReport();
}

private DynamicDataSet GetData(string query, ReportDocument crystalReport)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        DynamicDataSet dsCustomers = new DynamicDataSet();
        cmd.Connection = con;
        con.Open();
        using (SqlDataReader sdr = cmd.ExecuteReader())
        {
            //Get the List of all TextObjects in Section2.
            List<TextObject> textObjects = crystalReport.ReportDefinition.Sections["Section2"].ReportObjects.OfType<TextObject>().ToList();
            for (int i = 0; i < textObjects.Count; i++)
            {
                //Set the name of Column in TextObject.
                textObjects[i].Text = string.Empty;
                if (sdr.FieldCount > i)
                {
                    textObjects[i].Text = sdr.GetName(i);
                }
            }
            //Load all the data rows in the Dataset.
            while (sdr.Read())
            {
                DataRow dr = dsCustomers.Tables[0].Rows.Add();
                for (int i = 0; i < sdr.FieldCount; i++)
                {
                    dr[i] = sdr[i];
                }
            }
        }
        con.Close();
        return dsCustomers;
    }
}

private void btnCustomer_Click(object sender, EventArgs e)
{
    this.BindReport("SELECT TOP 5 CustomerID 'Id',ContactName 'Name',City,Country,Phone,Fax FROM Customers");
}

private void btnEmployee_Click(object sender, EventArgs e)
{
    this.BindReport("SELECT TOP 5 EmployeeID 'Id',FirstName+''+LastName 'Name',City+','+Country 'Address',HomePhone 'Phone' FROM Employees");
}

VB.Net

Private Sub BindReport(ByVal query As String)
    Dim crystalReport As ReportDocument = New ReportDocument()
    CrystalReportViewer1.DisplayGroupTree = False
    crystalReport.Load(Application.StartupPath.Replace("bin\Debug", "") & "DynamicReport.rpt")
    Dim dsCustomers As DynamicDataSet = GetData(query, crystalReport)
    crystalReport.SetDataSource(dsCustomers)
    Me.CrystalReportViewer1.ReportSource = crystalReport
    Me.CrystalReportViewer1.RefreshReport()
End Sub

Private Function GetData(ByVal query As String, ByVal crystalReport As ReportDocument) As DynamicDataSet
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim cmd As SqlCommand = New SqlCommand(query)

    Using con As SqlConnection = New SqlConnection(conString)
        Dim dsCustomers As DynamicDataSet = New DynamicDataSet()
        cmd.Connection = con
        con.Open()

        Using sdr As SqlDataReader = cmd.ExecuteReader()
            Dim textObjects As List(Of TextObject) = crystalReport.ReportDefinition.Sections("Section2").ReportObjects.OfType(Of TextObject)().ToList()
            For i As Integer = 0 To textObjects.Count - 1
                textObjects(i).Text = String.Empty
                If sdr.FieldCount > i Then
                    textObjects(i).Text = sdr.GetName(i)
                End If
            Next
            While sdr.Read()
                Dim dr As DataRow = dsCustomers.Tables(0).Rows.Add()
                For i As Integer = 0 To sdr.FieldCount - 1
                    dr(i) = sdr(i)
                Next
            End While
        End Using

        con.Close()
        Return dsCustomers
    End Using
End Function

Private Sub btnCustomer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCustomer.Click
    Me.BindReport("SELECT TOP 5 CustomerID 'Id',ContactName 'Name',City,Country,Phone,Fax FROM Customers")
End Sub

Private Sub btnEmployee_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEmployee.Click
    Me.BindReport("SELECT TOP 5 EmployeeID 'Id',FirstName+''+LastName 'Name',City+','+Country 'Address',HomePhone 'Phone' FROM Employees")
End Sub

Screenshot