In varchar column add numbers and show strings

  • msbisqldeveloper

    Valued Member

    Points: 63

    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

  • scdecade

    SSC Eights!

    Points: 807

    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;
  • Phil Parkin

    SSC Guru

    Points: 244449

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • msbisqldeveloper

    Valued Member

    Points: 63

    Thanks

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

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