change repeated fields [closed]

It's difficult to tell what is being asked here. Thi开发者_如何学JAVAs question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

table: tbl1

Fields:id,name

id       name

1        a
1        a
1        a
1        b

I like places that have been repeated name and Id(id=1,name=a || id=1,name=a || id=1,name=a).

change to

id=1,name=a1 || id=1,name=a2 || id=1,name=a3

By Linq && SqlServer

---

Note that this is my best attempt at interpreting the meaning of the question. Please respond if I am way off base :) I have proposed a solution using a one-time SQL query to update the output, as this is probably a little too complex for a LINQ solution.

Here is a query that will generate a table of id and names where the name is appended with an index value whenever the id and name are the same. It works by generating a row number and counting values that are identical. If the count of identical values is more than 1, then the row number is used to generate an index that is appended to the name. Note that this CANNOT modify the existing table, because it's impossible to update the records in this way when there is no unique identifier. You would have to output this into a new table and use that instead:

;with T as (
    select
        row_number() over (order by id, name) as idx,
        id,
        name,
        (select count(*) from [tbl1] b where b.id=a.id and b.name=a.name) as count
    from
        [tbl1] a
)
select
    id,
    case when
        count > 1 then
            name + 
                cast(
                    (select count(*) from T b where b.id=T.id and b.name=T.name
                        and b.idx<T.idx) + 1
                as varchar(50))
        else
            name
    end
from
    T

With this query, if tbl1 contained the following input:

1   a
1   a
1   a
1   b
2   a
2   a
2   b
2   b
2   b
2   c
2   c
2   d
1   e

The following output would be generated:

1   a1
1   a2
1   a3
1   b
1   e
2   a1
2   a2
2   b1
2   b2
2   b3
2   c1
2   c2
2   d

Please let me know if this did not answer your question. Hope this helps!