mohan111 mohan111 - 4 months ago 22
SQL Question

how to do partitioning on VARCHAR column

DECLARE @Table1 TABLE
(ID int, STATUS varchar(1))
;

INSERT INTO @Table1
(ID, STATUS)
VALUES
(1, 'A'),
(1, 'A'),
(1, 'A'),
(1, 'B'),
(1, 'A'),
(2, 'C'),
(2, 'C')
;


Script :

Select *,ROW_NUMBER()OVER(PARTITION BY STATUS ORDER BY (SELECT NULL))RN from @Table1


Getting Result Set

ID STATUS RN
1 A 1
1 A 2
1 A 3
1 A 4
1 B 1
2 C 1
2 C 2


Need Output

ID STATUS RN
1 A 1
1 A 2
1 A 3
1 B 1
1 A 1
2 C 1
2 C 2

Answer

Try this

DECLARE @Table1 TABLE 
        (ID int, STATUS varchar(1));

INSERT INTO @Table1
    (ID, STATUS)
VALUES
    (1,  'A'),
    (1,  'A'),
    (1,  'A'),
    (1,  'B'),
    (1,  'A'),
    (2,  'C'),
    (2,  'C');

;WITH Tmp
AS 
(    
    SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNumber FROM @Table1
)


SELECT
    A.ID ,
    A.STATUS ,      
    ROW_NUMBER() OVER (PARTITION BY A.STATUS, (A.RowNumber - A.RN)  ORDER BY (SELECT NULL)) AS RN
FROM
(
    Select *, ROW_NUMBER() OVER(PARTITION BY STATUS ORDER BY RowNumber) AS RN from tmp

) A
ORDER BY
    A.RowNumber

Output:

ID          STATUS RN
----------- ------ ------
1           A      1
1           A      2
1           A      3
1           B      1
1           A      1
2           C      1
2           C      2