T-SQL select rows between duplicated rows

Question

I have table Customer with these columns:

sales_ID varchar(50)
product_ID int
ProcessNumber int
CreateDate datetime

and this sample data:

sales_Id       product_id    ProcessNumber
---------------------------------------------  
00000020041       1733           15
00000020041       1733           15    
00000020041       1733           15
1522              1111           11
1522              1111           11
76002             1333           45
76002             1333           45

Question:

If I use this query

SELECT  *
FROM    
   (SELECT  
       *,
       CountOfRowsInGroup = COUNT(*) OVER (PARTITION BY sales_Id, product_ID, ProcessNumber)
    FROM Customer) AS tbl
WHERE   
    CountOfRowsInGroup > 1
ORDER BY 
    Sales_ID;

Query result:

    sales_Id       product_id    ProcessNumber
    ---------------------------------------------  
    00000020041       1733           15
    00000020041       1733           15    
    00000020041       1733           15
    1522              1111           11
    1522              1111           11
    76002             1333           45
    76002             1333           45

However I want result display this result with duplicated rows, as below

sales_Id       product_id    ProcessNumber
---------------------------------------------  
DUPLICATED ROW
00000020041       1733           15
00000020041       1733           15    
00000020041       1733           15
DUPLICATED ROW
1522              1111           11
1522              1111           11
DUPLICATED ROW
76002             1333           45
76002             1333           45

Thanks

Answer 1

From the query result you got,you can append the below query to get your desired output. Here I have taken the ranking as DENSE_RANK to get unique id's for each sales_id. For DUPLICATE ROW values, the ranking will be 1,2,3 etc and for the existing values, the ranking will be in the order 1.1,2.1,3.1 etc.

Then I use UNION ALL to combine the result of existing values and DUPLICATE ROW and the ORDER BY will be in the format 1,1.2,2,2.1 .... etc.

;WITH CTE AS
(
    SELECT DENSE_RANK() OVER(ORDER BY sales_Id)+0.1 RNO,
    DENSE_RANK() OVER(ORDER BY sales_Id) RNO2,'DUPLICATE ROW' DUP,* 
    FROM #TEMP
)
,CTE2 AS
(
    SELECT DISTINCT RNO2 AS ROWNUM,CAST(DUP AS VARCHAR(30)) sales_Id ,NULL product_id,NULL ProcessNumber
    FROM CTE
    UNION ALL
    SELECT RNO,CAST(sales_Id AS VARCHAR(30)),product_id,ProcessNumber
    FROM CTE
)
SELECT sales_Id,product_id,ProcessNumber
FROM CTE2
ORDER BY ROWNUM

• SQL FIDDLE

Note that the table I created is the result from your sample query in your question. You need to combine them.