In varchar column add numbers and show strings

  • Below is the sample data

    create table temp (userid int , rid varchar(10), value varchar(100))

    insert into temp(userid,rid,value)

    values(1,'D01','3'),

    (1,'D01','4'),

    (2,'C01','hey'),

    (2,'C01','1')

    expected output:

    1,'D01','7'

    2,'C01','hey'

    2,'C01','1'

    I tried below code and it is throwing error

    select distinct userid,rid,

    case when ISNUMERIC(value) = 1

    THEN

    SUM(cast(value as int))

    over (partition by userId,rid order by userid)

    else value

    end as [Value]

    from temp

  • drop table if exists #temp;
    go
    create table #temp (
    userid int , rid varchar(10), value varchar(100));

    insert #temp(userid,rid,value) values
    (1,'D01','3'),
    (1,'D01','4'),
    (2,'C01','hey'),
    (2,'C01','1');

    with t_cte(userid,rid,value) as (
    select
    userid, rid, cast(sum(cast(value as int)) as varchar)
    from
    #temp
    where
    isnumeric(value)=1
    group by
    userid, rid
    union all
    select * from #temp where isnumeric(value)=0)
    select * from t_cte order by 1, 2;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • This look very similar to this post. Why did you post again?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply