Counter for each row when using UNION

Hursh 191 Reputation points
2022-07-20T06:55:03.263+00:00

How do I get the following query to return RCounter in sequence like
1
2
3
4

SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RCounter,
Descr, Notes WHERE Type='A'

UNION

ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RCounter,
Descr, Notes WHERE Type = 'B'

It returns
RCounter Descr Notes
1 My Description1 This is a test
1 My Description2 Another test
2 My DescriptionA This is a testA
2 My DescriptionB Another testA

DDL:
DECLARE @test TABLE (Descr VARCHAR(MAX), Notes VARCHAR(MAX));
INSERT @test VALUES
('My Description1', 'This is a test'),
('My Description2', 'Another test'),
('My DescriptionA', 'This is a testA'),
('My DescriptionB', 'Another testA');

select * from @test

Developer technologies | Transact-SQL
{count} votes

3 answers

Sort by: Most helpful
  1. Wilko van de Velde 2,236 Reputation points
    2022-07-20T07:06:38.467+00:00

    Or if you want it with the union:

    DECLARE @test TABLE (Descr VARCHAR(MAX), Notes VARCHAR(MAX), Type VARCHAR(MAX));  
     INSERT @test VALUES  
     ('My Description1', 'This is a test','A'),  
     ('My Description2', 'Another test','B'),  
     ('My DescriptionA', 'This is a testA','A'),  
     ('My DescriptionB', 'Another testA','B');  
          
     select * from @test  
          
      
    SELECT   
    	ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RCounter,  
    	*  
    FROM  
    (  
    	SELECT  
    	Descr, Notes   
    	from @test   
    	WHERE Type='A'  
      
    	UNION  
      
    	SELECT  
    	Descr, Notes   
    	from @test  
    	WHERE Type = 'B'  
    ) x  
    
    1 person found this answer helpful.
    0 comments No comments

  2. Wilko van de Velde 2,236 Reputation points
    2022-07-20T07:00:08.1+00:00

    Not sure what you want to see, but:

    DECLARE @test TABLE (Descr VARCHAR(MAX), Notes VARCHAR(MAX), Type VARCHAR(MAX));  
    INSERT @test VALUES  
    ('My Description1', 'This is a test','A'),  
    ('My Description2', 'Another test','B'),  
    ('My DescriptionA', 'This is a testA','A'),  
    ('My DescriptionB', 'Another testA','B');  
      
    select * from @test  
      
    SELECT  
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RCounter,  
    Descr, Notes   
    from @test  
      
    
    0 comments No comments

  3. Bert Zhou-msft 3,436 Reputation points
    2022-07-20T08:33:30.907+00:00

    This is similar to the requirements of this link, the second solution of wilkovdv is more suitable. Please try it.

    Bert Zhou

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.