C#

E [myDB]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[uspGetPivotedData]

SELECT	'Return Value' = @return_value

GO

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

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(YEAR(t.EventYear))
            FROM Transactions t
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');
 
set @query = N'WITH CTE AS
(
    SELECT
        t.TransactionID, 
        m.memberName,
        m.date_registered, 
        envelopeNumber,
        registrationFee,
        t.Balance,
        YEAR(eventyear) eventyear,
        t.Amount
    FROM
        dbo.Transactions AS t 
        INNER JOIN dbo.Members AS m 
        ON t .MemberID = m.MemberID 
        INNER JOIN dbo.PaymentTypes AS p 
        ON t .TypeID = p.PaymentTypeID
)
SELECT
    TransactionID,
    memberName,
    date_registered, 
    envelopeNumber,
    registrationFee,
    Balance, 
' + @cols + N'
FROM
    CTE
PIVOT
    (
        MAX(Amount)
        FOR eventyear IN (' + @cols + N')
    ) p 
ORDER BY 
    TransactionID';

EXECUTE sp_executesql @query;

private DataTable GetData(string selectCommand, CommandType commandType, params SqlParameter[] parameters)
{
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Members"].ConnectionString))
    using (SqlCommand command = new SqlCommand(commandText, connection))
    {
        command.CommandType = commandType;
        
        foreach (ICloneable p in parameters)
        {
            command.Parameters.Add((SqlParameter)p.Clone());
        }
        
        SqlDataAdapter da = new SqlDataAdapter(command);
        DataTable result = new DataTable();
        da.Fill(result);
        return result;
    }
}

private void LoadPivotedData()
{
    try
    {
        DataTable table = GetData("dbo.uspGetPivotedData", CommandType.StoredProcedure);
        bindingSource1.DataSource = table;
        dataGridView2.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
    }
    catch (SqlException)
    {
        MessageBox.Show("There is an issue with connection string.");
    }
}

private void Form2_Load(object sender, System.EventArgs e)
{
    dataGridView2.DataSource = bindingSource1;
    LoadPivotedData();
}

private void reloadButton_Click(object sender, System.EventArgs e)
{
    LoadPivotedData();
}

Error	CS0103	The name 'commandText' does not exist in the current context

using (SqlCommand command = new SqlCommand(commandText, connection))

[Flags]
enum LP_InternetConnectionState : int {
    /// <summary>Local system has a valid connection to the Internet, but it might
    /// or might not be currently connected.</summary>
    Configured=0x40,
    /// <summary>Local system uses a local area network to connect to the Internet.</summary>
    LAN=0x02,
    /// <summary>Local system uses a modem to connect to the Internet.</summary>
    Modem=0x01,
    /// <summary>Local system is in offline mode.</summary>
    OffLine=0x20,
    /// <summary>Local system uses a proxy server to connect to the Internet.</summary>
    Proxy=0x04,
    /// <summary>Local system has RAS installed.</summary>
    RAS_installed=0x10
}


    [DllImport("wininet.dll")]
    static extern bool InternetGetConnectedStateEx(ref LP_InternetConnectionState lpdwFlags,
        StringBuilder name, int nameLength, int dwReserved);

Ping ping=new Ping();
byte[] bytes=new byte[32];
IPAddress address=GetIpFromHost(host);
PingReply reply = ping.Send(address, timeout, bytes);
ipStatus=(int)reply.Status;

OriginalGriff wrote:

any more than Ham and Pineapple