Counting the number of Diagnosis codes by context ,Specialty and provider

Question

Hi Team,

Hope you are doing well!.. I am trying to I am trying to count the number of diagnosis codes by context ,Specialty and provider..Can you please help me here.. Please find below the input and the output tables .
Input table

create table ##input
(contextid int,
visitbillid int ,
specialty varchar (20),
provider varchar (20),
diagnosiscode varchar (1000)
)

insert into ##input values
('78','34','OB','TITLO','Z01419,Z951,E663,M810'),
('78','35','OB','TITLO','Z01419,N952,E663,E559'),
('78','36','OB','TITLO','Z01419,E559,E663,Z1151,Z780,Z86010'),
('78','37','OB','TITLO','Z01411,N952,E663,E559,Z780,Z713'),
('712','89','Medicine','JAGLO','Z00129,Z130,Z1388,Z713,Z6853'),
('712','90','Medicine','JAGLO','Z00129,Z713,Z7182,Z6852'),
('712','91','Medicine','JAGLO','Z00121,H50011,Z713,Z6851,Z7182'),
('712','92','Medicine','JAGLO','H6693,T85628A,Y838')

Output table

create table ##output
(contextid int,
specialty varchar(20),
provider varchar(20),
diagnosiscode varchar(500),
countofdiagnosiscode int)

insert into ##output values

('78','OB','TITLO','Z01419','3'),
('78','OB','TITLO','Z951','1'),
('78','OB','TITLO','E663','4'),
('78','OB','TITLO','M810','1'),
('78','OB','TITLO','N952','2'),
('78','OB','TITLO','E559','3'),
('78','OB','TITLO','Z1151','1'),
('78','OB','TITLO','Z780','2'),
('78','OB','TITLO','286010','1'),
('78','OB','TITLO','Z713','1'),
('712','Medicine','JAGLO','Z00129','2'),
('712','Medicine','JAGLO','Z130','1'),
('712','Medicine','JAGLO','Z1388','1'),
('712','Medicine','JAGLO','Z713','3'),
('712','Medicine','JAGLO','Z6853','1'),
('712','Medicine','JAGLO','Z7182','2'),
('712','Medicine','JAGLO','Z6852','1'),
('712','Medicine','JAGLO','Z00121','1'),
('712','Medicine','JAGLO','H50011','1'),
('712','Medicine','JAGLO','Z6851','1'),
('712','Medicine','JAGLO','Z7182','1'),
('712','Medicine','JAGLO','H6693','1'),
('712','Medicine','JAGLO','T85628A','1'),
('712','Medicine','JAGLO','Y838','1')

Thanks,
Arun

Answer 1

Check this query:

select contextid, specialty, provider, t.value as diagnosiscode , count(*) as countofdiagnosiscode 
from ##input
cross apply string_split(diagnosiscode, ',') t
group by contextid, specialty, provider, t.value
order by contextid, specialty, provider, diagnosiscode

Prepend 'insert into...' to insert the results to ##output table.

Answer 2

Hi @Hellothere8028 ,

Welcome to Microsoft Q&A!

Please refer below:

;with cte as (  
select contextid,specialty,provider,value diagnosiscode  
 from ##input  
 cross apply STRING_SPLIT(diagnosiscode, ','))  
 select distinct  contextid,specialty,provider,diagnosiscode  
 ,count(diagnosiscode) over (partition by  contextid,specialty,provider,diagnosiscode) countofdiagnosiscode  
 from cte   
order by contextid,specialty  

If your version is under SQL Server 2016, you could use user-defined function instead of STRING_SPLIT.

CREATE FUNCTION [dbo].[SplitString]  
(  
    @List NVARCHAR(MAX),  
    @Delim VARCHAR(255)  
)  
RETURNS TABLE  
AS  
    RETURN ( SELECT [Value] FROM   
      (   
        SELECT   
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],  
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))  
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)  
          FROM sys.all_objects) AS x  
          WHERE Number <= LEN(@List)  
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim  
      ) AS y  
    );  

Then call this function as below:

;with cte as (  
select contextid,specialty,provider,value diagnosiscode  
 from ##input  
 cross apply [dbo].[SplitString](diagnosiscode, ','))  
 select distinct  contextid,specialty,provider,diagnosiscode  
 ,count(diagnosiscode) over (partition by  contextid,specialty,provider,diagnosiscode) countofdiagnosiscode  
 from cte   
order by contextid,specialty  

Best regards
Melissa

---

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