Normalized Table AD_2019_ALL_SYMP_NORM

The table, VAERSDATA, contains denormalized data of the symptoms. That is, each symptom is listed on the same row.
ie. SYM1, SYM2, SYM3, … SYM8

Denormalized data makes creating some reports easy.

But to do calculations, normalized data is much easier to work with, and more accurate.

Here are the scripts to create the normalized
View: V_AD_2019_ALL_SYMP_NORM
and
Table: AD_2019_ALL_SYMP_NORM

—

/*

Script to take the 
denormalized symptom data 
from table VAERSDATA

And insert it into a
normalized table 
AD_2019_ALL_SYMP_NORM

*/


use VAERS_2019;
go 


/*

drop view
V_AD_2019_ALL_SYMP_NORM;

*/



Create view V_AD_2019_ALL_SYMP_NORM
as
select 
  VAERS_ID
, symp         as symptom
from 
(
SELECT VAERS_ID
, SYM01             as symp
from dbo.[VAERSDATA] 
where SYM01 is not null
and   len(rtrim ( SYM01 )) > 0
Union 
SELECT VAERS_ID
, SYM02   as symp
from dbo.[VAERSDATA] 
where SYM02 is not null
and   len(rtrim ( SYM02 )) > 0
Union 
SELECT VAERS_ID
, sym03   as symp
from dbo.[VAERSDATA] 
where sym03 is not null
and   len(rtrim ( sym03 )) > 0
Union
SELECT VAERS_ID
, sym04   as symp
from dbo.[VAERSDATA] 
where sym04 is not null
and   len(rtrim ( sym04 )) > 0
Union
SELECT VAERS_ID
, sym05   as symp
from dbo.[VAERSDATA] 
where sym05 is not null
and   len(rtrim ( sym05 )) > 0
Union
SELECT VAERS_ID
, sym06   as symp
from dbo.[VAERSDATA] 
where sym06 is not null
and   len(rtrim ( sym06 )) > 0
Union
SELECT VAERS_ID
, sym07   as symp
from dbo.[VAERSDATA] 
where sym07 is not null
and   len(rtrim ( sym07 )) > 0
Union
SELECT VAERS_ID
, sym08   as symp
from dbo.[VAERSDATA] 
where sym08 is not null
and   len(rtrim ( sym08 )) > 0
Union
SELECT VAERS_ID
, sym09   as symp
from dbo.[VAERSDATA] 
where sym09 is not null
and   len(rtrim ( sym09 )) > 0
Union
SELECT VAERS_ID
, sym10   as symp
from dbo.[VAERSDATA] 
where sym10 is not null
and   len(rtrim ( sym10 )) > 0
Union
SELECT VAERS_ID
, sym11   as symp
from dbo.[VAERSDATA] 
where sym11 is not null
and   len(rtrim ( sym11 )) > 0
Union
SELECT VAERS_ID
, sym12   as symp
from dbo.[VAERSDATA] 
where sym12 is not null
and   len(rtrim ( sym12 )) > 0
Union
SELECT VAERS_ID
, sym13   as symp
from dbo.[VAERSDATA] 
where sym13 is not null
and   len(rtrim ( sym13 )) > 0
Union
SELECT VAERS_ID
, sym14   as symp
from dbo.[VAERSDATA] 
where sym14 is not null
and   len(rtrim ( sym14 )) > 0
Union
SELECT VAERS_ID
, sym15   as symp
from dbo.[VAERSDATA] 
where sym15 is not null
and   len(rtrim ( sym15 )) > 0
Union
SELECT VAERS_ID
, sym16   as symp
from dbo.[VAERSDATA] 
where sym16 is not null
and   len(rtrim ( sym16 )) > 0
Union
SELECT VAERS_ID
, sym17   as symp
from dbo.[VAERSDATA] 
where sym17 is not null
and   len(rtrim ( sym17 )) > 0
Union
SELECT VAERS_ID
, sym18   as symp
from dbo.[VAERSDATA] 
where sym18 is not null
and   len(rtrim ( sym18 )) > 0
Union
SELECT VAERS_ID
, sym19   as symp
from dbo.[VAERSDATA] 
where sym19 is not null
and   len(rtrim ( sym19 )) > 0
Union
SELECT VAERS_ID
, sym20   as symp
from dbo.[VAERSDATA] 
where sym20 is not null
and   len(rtrim ( sym20 )) > 0
)  as big_iv
;
go



/*

Drop table dbo.[AD_2019_ALL_SYMP_NORM]

*/



Select * 
into AD_2019_ALL_SYMP_NORM
from  V_AD_2019_ALL_SYMP_NORM;
go

—-

Test:

Select count(*)  Count
from dbo.[AD_2019_ALL_SYMP_NORM];
go

Count:
2,765,759