Display SQL Pivoted result in ASP.Net GridView using C# and VB.Net

Hi ihechi,

Please refer below sample code.

Database

Create table

Create Table Employees1 
(
  EmployeeID int IDENTITY(1,3) NOT NULL,
  employeeName nvarchar(150) NULL,
  empTitle nvarchar(100) NULL,
  email nvarchar(100) NULL
)
  
CREATE Table DateDetails 
(
  dateID int IDENTITY (1,1) NOT NULL,
  employeeID int NULL,
  dateCreated datetime NULL
)

Insert Data

INSERT INTO Employees1(employeeName,empTitle,email)
VALUES ('Jane Doe','Senior Assistant Attorney','jdoe@hotmail.com'),
('Karen Wise','Internal Auditor','kwise@yahoo.com'),
('Angel Marist','Director, Library','Amarist@gmail.com'),
('Kevin Wiseman','Library Director','	kwiseman@yahoo.com'),
('Major Bates','Director','mbates@bing.com'),
('May Baker','PRINCIPAL AUDITOR','mbaker@outlook.com');

INSERT INTO DateDetails (employeeID, dateCreated)
VALUES
(1,'2018-04-19 10:12:29.133'),
(4,NULL),
(7,'2018-04-24 17:22:39.143'),
(10,NULL),
(13,'2018-04-24 11:20:23.103'),
(16,'2018-04-24 12:22:29.133'),
(19,'2019-04-19 10:12:29.133'),
(22,'2019-04-24 12:22:29.130'),
(25,NULL),
(28,'2019-04-16 10:09:29.100'),
(31,'2019-04-24 11:20:23.103'),
(34,NULL),
(37,NULL),
(40,'2022-04-24 12:22:29.130'),
(43,'2022-04-24 17:22:39.143'),
(46,'2022-04-16 10:09:29.100'),
(49,'2022-04-24 11:20:23.103'),
(52,'2022-04-24 12:22:29.133'),
(55,'2023-04-19 10:12:29.133'),
(58,'2023-04-24 12:22:29.130'),
(61,'2023-04-24 17:22:39.143'),
(64,'2023-04-16 10:09:29.100'),
(67,'2023-04-24 11:20:23.103'),
(70,'2023-04-24 12:22:29.133');

Stored Procedure

CREATE PROCEDURE [dbo].[uspGetPivotedData] 
AS
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(FORMAT(d.dateCreated, 'yyyy')) 
            FROM DateDetails d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT employeeName,empTitle,email, ' + @cols + ' from 
            (
                select employeeName
                    , empTitle,email
                    , YEAR(dateCreated) AS YearCreated
                from Employees1
                inner join DateDetails on Employees1.employeeID = DateDetails.employeeID
           ) x
            pivot 
            (
                max(YearCreated)
                for YearCreated in (' + @cols + ')
            ) p '
EXECUTE sp_executesql @query

HTML

<asp:GridView ID="GridView" runat="server" AutoGenerateColumns="true" OnRowDataBound="OnRowDataBound">
</asp:GridView>

Namespaces

C#

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

VB.Net

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "uspGetPivotedData";
            cmd.Connection = con;
            con.Open();
            GridView.DataSource = cmd.ExecuteReader();
            GridView.DataBind();
            con.Close();
            con.Dispose();
        }
    }
}
protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        for (int i = 3; i < e.Row.Cells.Count; i++)
        {
            TableCell cell = e.Row.Cells[i];
            if (cell.Text != "&nbsp;")
            {
                cell.Text = "Submitted";
            }
            else
            {
                cell.Text = "No data";
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "uspGetPivotedData"
            cmd.Connection = con
            con.Open()
            GridView.DataSource = cmd.ExecuteReader()
            GridView.DataBind()
            con.Close()
            con.Dispose()
        End Using
    End Using
End Sub
Protected Sub OnRowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
    If e.Row.RowType = DataControlRowType.DataRow Then

        For i As Integer = 3 To e.Row.Cells.Count - 1
            Dim cell As TableCell = e.Row.Cells(i)

            If cell.Text <> "&nbsp;" Then
                cell.Text = "Submitted"
            Else
                cell.Text = "No data"
            End If
        Next
    End If
End Sub

Screenshot