Generating serial numbers

Question

i'm having a table called tblEmployeeNominees, this table consist the nominee member of the employee we can have more than one nominee for an employee, i want to display the serail numbers of the nominee members for the employee. if an employee has two nominee then it should have to show serial number for that employee as 1,2 if more than 2 or more then it have to show 1,2,3,..........

Answer 1

SELECT *, ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Nominee)
FROM tblEmployeeNominees;

Answer 2

Have a look at this. Works for sql server 2005

DECLARE @Table TABLE(
     EmpID INT,
     NomineeID INT
)

INSERT INTO @Table (EmpID,NomineeID) SELECT 1, 1
INSERT INTO @Table (EmpID,NomineeID) SELECT 1, 2
INSERT INTO @Table (EmpID,NomineeID) SELECT 2, 1
INSERT INTO @Table (EmpID,NomineeID) SELECT 3, 1
INSERT INTO @Table (EmpID,NomineeID) SELECT 3, 2
INSERT INTO @Table (EmpID,NomineeID) SELECT 3, 3

;WITH Serials AS (
    SELECT EmpID,
      MIN(NomineeID) MinNomineeID,
      CAST(MIN(NomineeID) AS VARCHAR(MAX)) Serial
    FROM @Table
    GROUP BY EmpID
    UNION ALL
    SELECT t.EmpID,
      t.NomineeID,
      s.Serial + ',' + CAST(NomineeID AS VARCHAR(MAX)) Serial
    FROM @Table t INNER JOIN
      Serials s ON t.EmpID = s.EmpID
         AND t.NomineeID > s.MinNomineeID
)
SELECT  Serials.EmpID,
     Serials.Serial
FROM    Serials INNER JOIN
     (
      SELECT EmpID,
        MAX(LEN(Serial)) LenSerial
      FROM Serials
      GROUP BY EmpID
     ) Lens ON Serials.EmpID = Lens.EmpID
WHERE   LEN(Serials.Serial) = Lens.LenSerial
ORDER BY 1
OPTION (MAXRECURSION 0)