Counter for each row when using UNION

Question

Counter for each row when using UNION

Hursh 191

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

Yitzhak Khabinsky 26,586 Reputation points

2022-07-20T12:28:06.643+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Bert Zhou-msft 3,436 Reputation points

2022-07-26T02:05:17.447+00:00

Have you tried the solution below and if you agree please accept it, it will help people with the same problem.

Bert Zhou

3 answers

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2022-07-20T12:28:06.643+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Bert Zhou-msft 3,436 Reputation points

2022-07-26T02:05:17.447+00:00

Have you tried the solution below and if you agree please accept it, it will help people with the same problem.

Bert Zhou

Answer 1

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

Answer 2

Wilko van de Velde 2,236

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

Answer 3

Bert Zhou-msft 3,436

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

Bert Zhou

Share via

Counter for each row when using UNION

3 answers

Your answer