Grouping in classic ASP

Question

I have a list of graduates in a mssql table with several years of renewals. I have been tasked with listing them by grad id and their renewal years separated by commas instead of one on each line. Some users have as many as 8 renewals. They are listed as such:

Lisa Jones 123456 2001 CMC
Lisa Jones 123456 2002 CMC
Lisa Jones 123456 2003 CMC

ultimately I would like to see:

Lisa Jones 123456 2001, 2002, 2003 CMC

Is there a way I can do this with a sql query or an asp function?? I have over 2300 records where the Grad ID is unique (123456) but the renewal dates are different. Grouped by grad id the count would be 275.

Answer 1

I would do this one in SQL, myself. Quite easy using the STUFF function in SQL Server 2008+.

I have mocked up an example using a table variable. You would swap the usage of the table variable for your real table named hubspot (replace @hubspot with hubspot).

Example setup (only needed to run the full example):

declare @hubspot table
(
    name varchar(100),
    grad_id int,
    renewal_year varchar(10),
    certification_type varchar(10)
)

insert into @hubspot values ('Lisa Jones', 123456, 2001, 'CMC')
insert into @hubspot values ('Lisa Jones', 123456, 2002, 'CMC')
insert into @hubspot values ('Lisa Jones', 123456, 2003, 'CMC')
insert into @hubspot values ('Lisa Jones', 123456, 2004, 'CMC')

Use this SQL on your table hubspot:

  select name
       , grad_id
       , STUFF((select ', ' + renewal_year
                  from @hubspot b 
                 where b.grad_id = a.grad_id
                 for xml path('')), 1, 2, '') as renewal_years
       , certification_type
    from @hubspot a
group by name
       , grad_id
       , certification_type

Output:

Documentation on STUFF function:

https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-2017