The thing you are looking for is "Window Functions" - there is an article here -
Window Functions in SQL Server - Simple Talk[
^]
I created your data in a table like this
create table demo
(
id int identity(1,1),
[Concatenate] nvarchar(125),
concatenatecount int,
result int
)
insert into demo values
('ABNS CONSTRUCTIONS BNREDDY NAGAR', NULL, 1),
('ABNS CONSTRUCTIONS BNREDDY NAGAR', NULL, 2),
('ABNS CONSTRUCTIONS BNREDDY NAGAR', NULL, 3),
('ACC LIMITED, MYSORE ROAD, BLR', NULL, 1),
('ACC LIMITED, MYSORE ROAD, BLR', NULL, 2),
('ASK ENTERPRISES', NULL, 1),
('ASK ENTERPRISES', NULL, 2),
('ASK ENTERPRISES', NULL, 3),
('ASK ENTERPRISES', NULL, 4)
With Window Functions you can specify how you want the data grouped using the
OVER
clause - in other words there is no need for a
GROUP BY
... Like this:
select id, [Concatenate], result,
COUNT(*) OVER (PARTITION BY [Concatenate]),
ROW_NUMBER() OVER (PARTITION BY [Concatenate] ORDER BY [Concatenate])
from demo
And that gave me the output:
1 ABNS CONSTRUCTIONS BNREDDY NAGAR 1 3 1
2 ABNS CONSTRUCTIONS BNREDDY NAGAR 2 3 2
3 ABNS CONSTRUCTIONS BNREDDY NAGAR 3 3 3
4 ACC LIMITED, MYSORE ROAD, BLR 1 2 1
5 ACC LIMITED, MYSORE ROAD, BLR 2 2 2
6 ASK ENTERPRISES 1 4 1
7 ASK ENTERPRISES 2 4 2
8 ASK ENTERPRISES 3 4 3
9 ASK ENTERPRISES 4 4 4
I included "result" just to check the expected results - it's the last two columns that contain the information you need