Change rows into binary string-CodePudding

I wonder if there is a better solution than the one I already did using SQL Server.

My table looks like this

RepID   ICD9_L1
----------------
1       AB
1       NJ
1       KL
2       TH
2       KL
2       EE
2       SR
3       AB
3       SR
....
95871   PY
95871   EE
95871   AB
95871   VX

I want to have a list of all codes and a binary string representation if it exists in for that RepID or not

AB : 1 (which means it exits for RepID 1),0 (which means it does not exist for RepID 2),1 (which means it exits for RepID 3),...,1 (which means it exits for RepID 95871)

NJ  :  1, 0, 0, ..., 0

KL  :  1, 1, 0, ..., 0

TH :   0, 1, 0, ..., 0

I built it using a look but it is very very slow

declare @T as table (ICD9_L1 varchar(100), StrExist varchar(max))
insert into @T (ICD9_L1)
select distinct ICD9_L1 FROM Diag


declare @i int = 0
declare @x varchar(max) = ''
declare @code varchar(10)

DECLARE db_cursor CURSOR FOR 
select ICD9_L1 FROM @T  

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @code  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    
    while @i < 70000
    begin
        set @i = @i   1

        if exists(select top 1 * FROM Diag where RepID = @i AND  ICD9_L1 = @code)
        begin
            set @x = @x   ',1'
        end
        else
        begin
            set @x = @x   ',0'
        end
    end

    update @T set StrExist = @x where ICD9_L1 = @code
    set @x = ''
      FETCH NEXT FROM db_cursor INTO @code 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

CodePudding user response：

use string_agg() to concatenate the 0, 1

Use a tally / number table generate a list of IDs for left join to table. You may also use recursive CTE to generate one dynamically.

The query:

with 
-- generate a list of IDs
numbers as
(
    select n = 1
    union all
    select n = n   1
    from   numbers
    where  n < 70000
),
codes as
(
    select distinct code = ICD9_L1
    from   Diag
)
select c.code, 
       string_agg((case when d.RepID is not null then '1' else '0' end), ',') 
                  within group (order by n.n)
from   codes c
       cross join numbers n
       left join Diag d   on  c.code = d.ICD9_L1
                          and n.n    = d.RepID
group by c.code