CODEWITHSUNDEEP
sql-server
Khadir
Khadir

Reputation:

Generating serial numbers

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,..........

Upvotes: 0

Views: 634

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294247

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

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166396

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)

Upvotes: 1

Related Questions