how to get number of duplicate value in sql?

Question

hi guys.

I have a table for keep history of a piping result. a pipe should weld and after weld I save the result of that weld : Acc Rej or some thing else.

Weld Result for a pipe :

• step 1 : repair
• step 2: repair
• step3:repair
• step4 :acc

now I want to query this history and get this result: how can I do that.

weldID   Status 
1431     RP3/ACC1

Answer 1

If the names are not fixed, then check this query too:

select WeldId,
    (
        select string_agg(c, '/') within group (order by s)
        from
        (
            select min(NdtStep) s, concat(ShortName, count(*)) as c
            from MyTable
            where WeldId = t.WeldId
            group by ShortName
        ) g
    )
from MyTable t
group by WeldId

If STRING_AGG is not available on your server, it can be replaced with another construct.

Answer 2

Hi @Farshad Valizade

You can try this query.

create table test(ResultId int,RequestId int,WeldId int,NdtStatusId int,NdtStep int,NdtVold int,
RepRemark varchar(10),NdtType varchar(20),NdtStatus varchar(20),ShortName varchar(10));
insert into test values
(2331,2225,1431,5,1,1,null,'RT','Repair','RP'),
(2333,2225,1431,5,3,1,null,'RT','Repair','RP'),
(2334,2225,1431,1,4,0,null,'RT','ACC','A'),
(2482,2225,1431,5,2,1,null,'RT','Repair','RP')

;with T1 as(
  select WeldId,ShortName,count(*) as nums from test group by WeldId,ShortName
),T2 as(
  select WeldId,case when ShortName = 'A' then 'ACC' else ShortName end as ShortName,nums 
  from T1
),T3 as(
  select WeldId,cast(ShortName as varchar) + cast(nums as varchar) as t from T2
),T4 as(
  select WeldId,case when left(t,1) = 'A' then t else null end as m,
  case when left(t,1) = 'R' then t else null end as n from T3
),T5 as(
  select WeldId,max(m) as m,max(n) as n from T4 group by WeldId)
select WeldId,n + '/' + m as Status from T5;

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.