It is possible to change how row_number inserts the values?-CodePudding

Currently I'm doing this:

    select  
        ProductID = ProductID = ROW_NUMBER() OVER (PARTITION BY PRODUCTID ORDER BY PRODUCtID),
        TransactionDate,
        TransactionAmount
    from ProductsSales
order by ProductID

The results are like this:

ProductID	TransactionDate	TransactionAmount
1	2022-11-06	30
2	2022-11-12	30
3	2022-11-28	30
2	2022-11-03	10
3	2022-11-10	10
4	2022-11-15	10
3	2022-11-02	50

The duplicated IDs are being inserted sequential, but what I need it to be like this:

ProductID	TransactionDate	TransactionAmount
1	2022-11-06	30
1.1	2022-11-12	30
1.2	2022-11-28	30
2	2022-11-03	10
2.1	2022-11-10	10
2.2	2022-11-15	10
3	2022-11-02	50

Is this possible?

CodePudding user response：

Assuming your PRODUCTID field is numeric already, then this should work:

WITH _ProductIdSorted AS
(
    SELECT
        CONCAT
        (
            PRODUCTID,
            '.',
            ROW_NUMBER() OVER (PARTITION BY PRODUCTID ORDER BY TransactionDate) - 1
        ) AS ProductId,
        TransactionDate,
        TransactionAmount
    FROM ProductsSales
)

SELECT
    REPLACE(ProductId, '.0', '') AS ProductId,
    TransactionDate,
    TransactionAmount
FROM _ProductIdSorted;

By the way, just the same as the ORDER BY clause in your query, the one my answer uses is a nondeterminsitic sort. It seems, based on your Post, it doesn't matter to you the order which the rows are sorted within the partition though.