Normalized Table AD_2019_ALL_VAX_NORM

The table, VAERSDATA, contains denormalized data of the vaccines given.
That is, each vaccine is listed on the same row.
ie. VAX1, VAX2, VAX3, … VAX8

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_VAX_NORM
and
Table: AD_2019_ALL_VAX_NORM

—-

/*

Script to take the 
denormalized vaccine/vax data 
from table VAERSDATA

Create view V_AD_2019_ALL_VAX_NORM

And insert the data into a
normalized table 
AD_2019_ALL_VAX_NORM

*/

use VAERS_2019;
go


/*
Drop view V_AD_2019_ALL_VAX_NORM
go
*/

Create view V_AD_2019_ALL_VAX_NORM
as
select
  VAERS_ID
, VAX_CNT
, vax
, VAXMAN
, VAXLOT
, VAXDOSE 
, VAXROUTE 
, VAXSITE 
, VAXNAME 
from 
(
select 
  VAERS_ID
, VAX_CNT
, VAX1	    	as vax
, VAX1MAN    	as VAXMAN
, VAX1LOT	as VAXLOT
, VAX1DOSE 	as VAXDOSE 
, VAX1ROUTE 	as VAXROUTE 
, VAX1SITE 	as VAXSITE 
, VAX1NAME 	as VAXNAME 
from dbo.[VAERSDATA] 
where VAX1 is not null
and   len(rtrim ( VAX1 )) > 0
union
select 
  VAERS_ID
, VAX_CNT
, VAX2	    	as vax
, VAX2MAN    	as VAXMAN
, VAX2LOT	as VAXLOT
, VAX2DOSE 	as VAXDOSE 
, VAX2ROUTE 	as VAXROUTE 
, VAX2SITE 	as VAXSITE 
, VAX2NAME 	as VAXNAME 
from dbo.[VAERSDATA] 
where VAX2 is not null
and   len(rtrim ( VAX2 )) > 0
union
select 
  VAERS_ID
, VAX_CNT
, VAX3	    	as vax
, VAX3MAN    	as VAXMAN
, VAX3LOT	as VAXLOT
, VAX3DOSE 	as VAXDOSE 
, VAX3ROUTE 	as VAXROUTE 
, VAX3SITE 	as VAXSITE 
, VAX3NAME 	as VAXNAME 
from dbo.[VAERSDATA] 
where VAX3 is not null
and   len(rtrim ( VAX3 )) > 0
union
select 
  VAERS_ID
, VAX_CNT
, VAX4	    	as vax
, VAX4MAN    	as VAXMAN
, VAX4LOT	as VAXLOT
, VAX4DOSE 	as VAXDOSE 
, VAX4ROUTE 	as VAXROUTE 
, VAX4SITE 	as VAXSITE 
, VAX4NAME 	as VAXNAME 
from dbo.[VAERSDATA] 
where VAX4 is not null
and   len(rtrim ( VAX4 )) > 0
union
select 
  VAERS_ID
, VAX_CNT
, VAX5	    	as vax
, VAX5MAN    	as VAXMAN
, VAX5LOT	as VAXLOT
, VAX5DOSE 	as VAXDOSE 
, VAX5ROUTE 	as VAXROUTE 
, VAX5SITE 	as VAXSITE 
, VAX5NAME 	as VAXNAME 
from dbo.[VAERSDATA] 
where VAX5 is not null
and   len(rtrim ( VAX5 )) > 0
union
select 
  VAERS_ID
, VAX_CNT
, VAX6	    	as vax
, VAX6MAN    	as VAXMAN
, VAX6LOT	as VAXLOT
, VAX6DOSE 	as VAXDOSE 
, VAX6ROUTE 	as VAXROUTE 
, VAX6SITE 	as VAXSITE 
, VAX6NAME 	as VAXNAME 
from dbo.[VAERSDATA] 
where VAX6 is not null
and   len(rtrim ( VAX6 )) > 0
union
select 
  VAERS_ID
, VAX_CNT
, VAX7	    	as vax
, VAX7MAN    	as VAXMAN
, VAX7LOT	as VAXLOT
, VAX7DOSE 	as VAXDOSE 
, VAX7ROUTE 	as VAXROUTE 
, VAX7SITE 	as VAXSITE 
, VAX7NAME 	as VAXNAME 
from dbo.[VAERSDATA] 
where VAX7 is not null
and   len(rtrim ( VAX7 )) > 0
union
select 
  VAERS_ID
, VAX_CNT
, VAX8	    	as vax
, VAX8MAN    	as VAXMAN
, VAX8LOT	as VAXLOT
, VAX8DOSE 	as VAXDOSE 
, VAX8ROUTE 	as VAXROUTE 
, VAX8SITE 	as VAXSITE 
, VAX8NAME 	as VAXNAME 
from dbo.[VAERSDATA] 
where VAX8 is not null
and   len(rtrim ( VAX8 )) > 0
)  as iv_all_vax
;
go



/*
drop table 
dbo.[AD_2019_ALL_VAX_NORM];
go
*/


Select * 
into AD_2019_ALL_VAX_NORM
from V_AD_2019_ALL_VAX_NORM;
go

—-

Test:

select count(*)  Count
as  AD_2019_ALL_VAX_NORM_count
from dbo.[AD_2019_ALL_VAX_NORM];
go

Count:
1,155,341