I have a table

and more tables from other store

The problem here is

1. all stores start with id 1
2. repeat customer have new id in id column and their original id is retained in repeat customer id column

I have to concatenated all the tables and also keep track of repeating customer for analytics. I have joined all tables using UNION ALL and also created a dummy id column using SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NEW_ID, * FROM CTE, but I have no clue how to capture and assign value to repeat customer id such that I get the table as below

CodePudding user response：

The best way to incorporate it, would be to use Alphanumeric String as NEW_ID, and concat STORE and ID to create NEW_ID. For example A_000000001. In that way you can add similar STORE to REPEAT_CUSTOMER_ID as well.

So in this case, instead of NEW_ID=6, you would have NEW_ID=B_000000003 and REPEAT_CUSTOMER_ID would become B_000000001.

But in case that is not possible, you can use query like below to get the output

DB Fiddle Query

with CTE as
(
select * from STORE1 
UNION ALL
select * from STORE2
)

,CTE2 as
(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NEW_ID,t.* from CTE t)

,REPEAT_ID as 
(select NEW_ID,ID,REPEAT_CUSTOMER_ID,STORE from CTE2 where REPEAT_CUSTOMER_ID is not null)

,REPEACT_CUSTOMER_ID as 
(select c.NEW_ID as NEW_REPEAT_CUSTOMER_ID,r.NEW_ID
from REPEAT_ID r
left join CTE2 c
on c.ID=r.REPEAT_CUSTOMER_ID and c.STORE=r.STORE
)

select c.* , n.NEW_REPEAT_CUSTOMER_ID
from CTE2 c
left join REPEACT_CUSTOMER_ID n
on c.NEW_ID=n.NEW_ID

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=cbe63994b10f9e3b0eff53b0c89d463a

SO basically you have to separate rows where REPEATE customer is present and join it with main table query.