CODEWITHSUNDEEP
c#asp.netdatatable
jtram
jtram

Reputation: 25

Displaying data that is grouped by an ID

I am using c#, asp.net, webforms, and Visual Studio. I am also using SQL Server Management Studio to create my stored procedure.

How can I group by an ID called RegId and then show a list of Title: URL for each RegId?

Example of my table: enter image description here

Stored Procedure:

USE [MyDB]
GO
/****** Object:  StoredProcedure [dbo].[spGetRegSess]    Script Date: 8/5/2021 7:31:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spGetRegSess]
       
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT *
    FROM RegSess
END

What I prefer to display in text format:

Bob has Google at http://www.google.com, Yahoo at http://www.yahoo.com, and MSN at http://www.msn.com.

John has Reddit at http://www.reddit.com.

I have this button on my webforms asp.net page:

 <asp:Button ID="btnSend" runat="server" OnClick="btnSend_Click" Text="Send" />

On my button click, I get the data from the stored procedure and put it into a datatable:

protected void btnSend_Click(object sender, EventArgs e){
    DataTable dataTable = new DataTable();

    //Fill datatable
    using (SqlConnection sqlConn = new 
         SqlConnection(ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString))
    {

        sqlConn.Open();

        using (var command = sqlConn.CreateCommand())
        {
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "spGetRegSess";

            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dataTable);
            sqlConn.Close();
            da.Dispose();
            var myCount = dataTable.Rows.Count;
        }
        sqlConn.Close();
    }
}

I am stuck after this part. I know I need to group by RegId but not sure the syntax to do it.

Upvotes: 0

Views: 178

Answers (1)

Drewskis
Drewskis

Reputation: 449

You can use XML Path and Stuff to concatenate the results as shown below:

SELECT 
  r.RegistrantId, 
  r.Name, 
  STUFF(
    (
      SELECT 
        ', ' + r2.Name + ' has ' + r2.[Whatever_Your_Column_Name_Is_here] + ' at ' + CAST(
          r2.Url AS VARCHAR(MAX)
        ) 
      FROM 
        RegSess r2 
      WHERE 
        (r2.RegistrantId = r.RegistrantId) FOR XML PATH(''), 
        TYPE
    ).value('(./text())[1]', 'VARCHAR(MAX)'), 
    1, 
    2, 
    ''
  ) AS URLs 
FROM 
  RegSess r 
GROUP BY 
  r.RegistrantId, 
  r.Name

If you want it grouped by session (Column title in your case), you would simply add it to your grouping like this:

SELECT 
  r.RegistrantId, 
  r.Name, 
  r.Title, 
  STUFF(
    (
      SELECT 
        ', ' + r2.Name + ' has ' + r2.[Whatever_Your_Column_Name_Is_here] + ' at ' + CAST(
          r2.Url AS VARCHAR(MAX)
        ) 
      FROM 
        RegSess r2 
      WHERE 
        (
          r2.RegistrantId = r.RegistrantId 
          and r2.Title = r.Title
        ) FOR XML PATH(''), 
        TYPE
    ).value('(./text())[1]', 'VARCHAR(MAX)'), 
    1, 
    2, 
    ''
  ) AS URLs 
FROM 
  RegSess r 
GROUP BY 
  r.RegistrantId, 
  r.Name, 
  r.Title

Credit to @kevin-fairchild and his answer to https://stackoverflow.com/a/273330/8304027

Upvotes: 1

Related Questions