I want to set the userid as "distinct_user_#" for the rows with same tenant ids. I can't set the userid manually as tenantids are generated randomly

So output would be something like

Any help with this?

CodePudding user response：

We can use DENSE_RANK() here:

SELECT 'd_u_'   CAST(DENSE_RANK() OVER (ORDER BY tenantid) AS varchar(12)) AS userid,
       tenantid
FROM yourTable;

CodePudding user response：

You can use the dense_rank function to generate this id:

SELECT 'd_u_'   DENSE_RANK() OVER (PARTITION BY tenantid ORDER BY tenantid) AS userid,
       tenantid
FROM   mytable