Assign Category to Varying Length of Rows

• Kenny B.

  Valued Member

  Points: 64

  More actions

  March 18, 2022 at 3:43 pm

  #4001965

  Using SQL Server 2012. I have to produce three lists for each date based on three dates. I won't to assign a letter A,B or C to each output row based on the date. The number of rows will vary by date. Thank you in advance.

  Output:

  

  Data:

  CREATE TABLE #s(

  Date DATE NOT NULL

  ,ID INTEGER NOT NULL PRIMARY KEY

  );

  INSERT INTO #s(Date,ID) VALUES ('4/7/2022',1);

  INSERT INTO #s(Date,ID) VALUES ('4/7/2022',4);

  INSERT INTO #s(Date,ID) VALUES ('4/7/2022',7);

  INSERT INTO #s(Date,ID) VALUES ('4/7/2022',10);

  INSERT INTO #s(Date,ID) VALUES ('4/7/2022',13);

  INSERT INTO #s(Date,ID) VALUES ('4/7/2022',16);

  INSERT INTO #s(Date,ID) VALUES ('4/7/2022',19);

  INSERT INTO #s(Date,ID) VALUES ('4/8/2022',2);

  INSERT INTO #s(Date,ID) VALUES ('4/8/2022',5);

  INSERT INTO #s(Date,ID) VALUES ('4/8/2022',8);

  INSERT INTO #s(Date,ID) VALUES ('4/8/2022',11);

  INSERT INTO #s(Date,ID) VALUES ('4/8/2022',14);

  INSERT INTO #s(Date,ID) VALUES ('4/8/2022',17);

  INSERT INTO #s(Date,ID) VALUES ('4/9/2022',3);

  INSERT INTO #s(Date,ID) VALUES ('4/9/2022',6);

  INSERT INTO #s(Date,ID) VALUES ('4/9/2022',9);

  INSERT INTO #s(Date,ID) VALUES ('4/9/2022',12);

  INSERT INTO #s(Date,ID) VALUES ('4/9/2022',15);

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  March 18, 2022 at 5:17 pm

  #4001996

  SELECT Date,ID,CHAR(ASCII('A') + (ROW_NUMBER() OVER(PARTITION BY Date ORDER BY ID) -1) % 3) AS Grp
  FROM #s
  ORDER BY Date,ID;
  

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Kenny B.

  Valued Member

  Points: 64

  More actions

  March 18, 2022 at 5:44 pm

  #4002001

  Perfect. Thank you, KB