Hope this can get you started:

create table #T(
  ID varchar(3),
  diag1 varchar(10),
  diag2 varchar(10),
  diag3 varchar(10),
  diag4 varchar(10),
  diag5 varchar(10),

One, that means the table has as many columns needed for all the...

The only answer I can come up with without reading anything about sequences (haven't used them yet) is that the sequence could possibly be reset therefore resulting in duplicate values.

Like this, for one method:

create table dbo.test1
([Category] char(1) null,
 [CNT] int null
);

create table dbo.test2
([Category] char(1) null);

insert into dbo.test1 ([Category], [CNT])
values ('A', 5),('B',...