how to eliminate duplicate column value in sql server?

Question

hi every body

I have this welder list data and I want to show count of each column value and remove duplicate in it.

see this screenshot:

I want to have count of each welder in this result :

GTA1GTA2SMA1SMA22123HOW CAN i DO THAT?

Answer 1

Check this query:

select welder, count(distinct c) as [count]
from MyTable
cross apply (values  
    ('GTA1', GTA1), 
    ('GTA2', GTA2),
    ('SMA1', SMA1),
    ('SMA2', SMA2)
) w(welder, c)
group by welder

Answer 2

Hi @Farshad Valizade

Please check this sample:

Declare @temp table (id int, GTA1 varchar(20), GTA2 varchar(20), SMA1 varchar(20), SMA2 varchar(20));
insert into @temp values
(1,'002','001','003','004'),
(2,'','001','',''),
(3,'001','001','002','002'),
(4,'','001','002','004')

SELECT COUNT(DISTINCT NULLIF(GTA1,'')) AS GTA1,
       COUNT(DISTINCT NULLIF(GTA2,'')) AS GTA2,
       COUNT(DISTINCT NULLIF(SMA1,'')) AS SMA1,
       COUNT(DISTINCT NULLIF(SMA2,'')) AS SMA2
FROM @temp

Output:

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. 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.