get unique records based on string

Asked by At

I have a table that has records like below.

Table:

ID      Name            Value
1       xxx AA           10
2       yyy B            20
3       AA zzz           11
4       xxx B            20
5       kkk C            30
6       qqq (AA) ZZ      10 
7        AA              10

I would like to get common records and add values of those records. The expected output table is:

TableOut

ID      Name    Value
1       AA        41
2       B         40
3       C         30 

The above is just examples of few records. However, the table has more than 100 records and it is possible that, in future, records will be added dynamically. Note: I'm OK, if it picks up sub string those are duplicate and read this as single records.

How can I achieve this?

4 Answers

0
Carlos Alves Jorge On

EDIT:

SELECT SUBSTRING(name, CHARINDEX(' ', name) +1, 20), SUM(Value)
from table
group by SUBSTRING(name, CHARINDEX(' ', name) +1, 20)
0
Sayed idrees On

Try this..

select SUBSTRING(Name, CHARINDEX(' ', Name),3) AS region, SUM(value) as Value 
from example
group by (SUBSTRING(Name, CHARINDEX(' ', Name),3))

the First Column is Actually a serial Number

1
Michał Turczyn On

Try this:

-- sample data
declare @tbl table (ID int, Name varchar(10), Value int);
insert into @tbl values
(1, 'xxx AA', 10),
(2, 'yyy B', 20),
(3, 'zzz AA', 11),
(4, 'xxx B', 20),
(5, 'kkk C', 30);
-- select query
select row_number() over (order by NewName) ID,
       NewName, 
       sum(Value) 
from (
    select substring(Name, charindex(' ', Name) + 1, len(Name)) NewName,
           Value
    from @tbl
) a group by NewName
0
Larnu On

How I'd do it, using STUFF:

WITH VTE AS(
    SELECT *
    FROM (VALUES(1,'xxx AA',10),
                (2,'yyy B',20),
                (3,'zzz AA',11),
                (4,'xxx B',20),
                (5,'kkk C',30)) V(ID,[Name],[Value]))
SELECT ROW_NUMBER() OVER (ORDER BY N.R) AS ID,
       N.R AS [Name],
       SUM([Value]) AS [Value]
FROM VTE V
     CROSS APPLY (VALUES(STUFF(V.[Name],1,CHARINDEX(' ',V.[Name]),''))) N(R)
GROUP BY N.R
ORDER BY N.R;