Displaying data that is grouped by an ID

Question

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:

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.

Can you share your stored procedure and also the Web Form code that is using the data table? — Drewskis, Aug 05 '21 at 13:17

Drewskis · Accepted Answer · 2021-08-05T14:44:55.890

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

Thank you. I just tried this, and it is giving me some strange results. Here is what happens: https://ibb.co/qWJbgT0 — jtram, Aug 05 '21 at 14:24
That was most likely caused by the title being part of the outer grouping. Revised query above. — Drewskis, Aug 05 '21 at 14:39

Displaying data that is grouped by an ID

1 Answers1