March 22, 2018 at 6:12 am
I have a stored procedure that uses dynamic sql to create a pivot table. I need to rearrange the columns like this..........ID, diag1, poa1, diag2, poa2,diag3, poa3, diag4, poa4, diag5, poa5, etc. This needs to be dynamic as well because the number of columns, diag and poa, is not known.
create table #T
(
ID varchar(3),
diag1 varchar(10),
diag2 varchar(10),
diag3 varchar(10),
diag4 varchar(10),
diag5 varchar(10),
poa1 varchar(10),
poa2 varchar(10),
poa3 varchar(10),
poa4 varchar(10),
poa5 varchar(10),
)
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('001','R07.89','E11.9','E03.9','E78.5','I10','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('002','R07.90','E11.10','E03.10','E78.6','I11','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('003','R07.91','E11.11','E03.11','E78.7','I12','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('004','R07.92','E11.12','E03.12','E78.8','I13','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('005','R07.93','E11.13','E03.13','E78.9','I14','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('006','R07.94','E11.14','E03.14','E78.10','I15','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('007','R07.95','E11.15','E03.15','E78.11','I16','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('008','R07.96','E11.16','E03.16','E78.12','I17','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('009','R07.97','E11.17','E03.17','E78.13','I18','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('010','R07.98','E11.18','E03.18','E78.14','I19','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('011','R07.99','E11.19','E03.19','E78.15','I20','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('012','R07.100','E11.20','E03.20','E78.16','I21','E','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('013','R07.101','E11.21','E03.21','E78.17','I22','E','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('014','R07.102','E11.22','E03.22','E78.18','I23','E','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('015','R07.103','E11.23','E03.23','E78.19','I24','E','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('016','R07.104','E11.24','E03.24','E78.20','I25','E','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('017','R07.105','E11.25','E03.25','E78.21','I26','E','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('018','R07.106','E11.26','E03.26','E78.22','I27','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('019','R07.107','E11.27','E03.27','E78.23','I28','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('020','R07.108','E11.28','E03.28','E78.24','I29','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('021','R07.109','E11.29','E03.29','E78.25','I30','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('022','R07.110','E11.30','E03.30','E78.26','I31','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('023','R07.111','E11.31','E03.31','E78.27','I32','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('024','R07.112','E11.32','E03.32','E78.28','I33','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('025','R07.113','E11.33','E03.33','E78.29','I34','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('026','R07.114','E11.34','E03.34','E78.30','I35','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('027','R07.115','E11.35','E03.35','E78.31','I36','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('028','R07.116','E11.36','E03.36','E78.32','I37','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('029','R07.117','E11.37','E03.37','E78.33','I38','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('030','R07.118','E11.38','E03.38','E78.34','I39','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('031','R07.119','E11.39','E03.39','E78.35','I40','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('032','R07.120','E11.40','E03.40','E78.36','I41','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('033','R07.121','E11.41','E03.41','E78.37','I42','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('034','R07.122','E11.42','E03.42','E78.38','I43','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('035','R07.123','E11.43','E03.43','E78.39','I44','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('036','R07.124','E11.44','E03.44','E78.40','I45','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('037','R07.125','E11.45','E03.45','E78.41','I46','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('038','R07.126','E11.46','E03.46','E78.42','I47','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('039','R07.127','E11.47','E03.47','E78.43','I48','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('040','R07.128','E11.48','E03.48','E78.44','I49','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('041','R07.129','E11.49','E03.49','E78.45','I50','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('042','R07.130','E11.50','E03.50','E78.46','I51','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('043','R07.131','E11.51','E03.51','E78.47','I52','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('044','R07.132','E11.52','E03.52','E78.48','I53','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('045','R07.133','E11.53','E03.53','E78.49','I54','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('046','R07.134','E11.54','E03.54','E78.50','I55','Y','Y','Y','N','N')
March 22, 2018 at 6:58 am
Try searching for "dynamic pivot" and see what you can find, that will handle your issue. Alternatively, you could impose some sort of practical limit, 15 or 20 diagnosis codes would handle 99% of institutional or professional claims, and you could just display all codes whether present or not, without having to make it dynamic. Since diagnosis codes are supposed to be entered in the order of importance to the episode at hand, data loss should be very minimal.
Plus, just because you're not pivoting the dx for display doesn't mean you can't accept it into the system, should anyone ask for it down the line.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
March 22, 2018 at 7:05 am
NineIron - Thursday, March 22, 2018 6:12 AMI have a stored procedure that uses dynamic sql to create a pivot table. I need to rearrange the columns like this..........ID, diag1, poa1, diag2, poa2,diag3, poa3, diag4, poa4, diag5, poa5, etc. This needs to be dynamic as well because the number of columns, diag and poa, is not known.
create table #T
(
ID varchar(3),
diag1 varchar(10),
diag2 varchar(10),
diag3 varchar(10),
diag4 varchar(10),
diag5 varchar(10),
poa1 varchar(10),
poa2 varchar(10),
poa3 varchar(10),
poa4 varchar(10),
poa5 varchar(10),
)
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('001','R07.89','E11.9','E03.9','E78.5','I10','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('002','R07.90','E11.10','E03.10','E78.6','I11','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('003','R07.91','E11.11','E03.11','E78.7','I12','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('004','R07.92','E11.12','E03.12','E78.8','I13','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('005','R07.93','E11.13','E03.13','E78.9','I14','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('006','R07.94','E11.14','E03.14','E78.10','I15','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('007','R07.95','E11.15','E03.15','E78.11','I16','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('008','R07.96','E11.16','E03.16','E78.12','I17','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('009','R07.97','E11.17','E03.17','E78.13','I18','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('010','R07.98','E11.18','E03.18','E78.14','I19','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('011','R07.99','E11.19','E03.19','E78.15','I20','Y','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('012','R07.100','E11.20','E03.20','E78.16','I21','E','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('013','R07.101','E11.21','E03.21','E78.17','I22','E','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('014','R07.102','E11.22','E03.22','E78.18','I23','E','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('015','R07.103','E11.23','E03.23','E78.19','I24','E','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('016','R07.104','E11.24','E03.24','E78.20','I25','E','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('017','R07.105','E11.25','E03.25','E78.21','I26','E','N','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('018','R07.106','E11.26','E03.26','E78.22','I27','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('019','R07.107','E11.27','E03.27','E78.23','I28','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('020','R07.108','E11.28','E03.28','E78.24','I29','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('021','R07.109','E11.29','E03.29','E78.25','I30','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('022','R07.110','E11.30','E03.30','E78.26','I31','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('023','R07.111','E11.31','E03.31','E78.27','I32','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('024','R07.112','E11.32','E03.32','E78.28','I33','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('025','R07.113','E11.33','E03.33','E78.29','I34','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('026','R07.114','E11.34','E03.34','E78.30','I35','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('027','R07.115','E11.35','E03.35','E78.31','I36','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('028','R07.116','E11.36','E03.36','E78.32','I37','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('029','R07.117','E11.37','E03.37','E78.33','I38','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('030','R07.118','E11.38','E03.38','E78.34','I39','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('031','R07.119','E11.39','E03.39','E78.35','I40','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('032','R07.120','E11.40','E03.40','E78.36','I41','E','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('033','R07.121','E11.41','E03.41','E78.37','I42','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('034','R07.122','E11.42','E03.42','E78.38','I43','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('035','R07.123','E11.43','E03.43','E78.39','I44','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('036','R07.124','E11.44','E03.44','E78.40','I45','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('037','R07.125','E11.45','E03.45','E78.41','I46','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('038','R07.126','E11.46','E03.46','E78.42','I47','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('039','R07.127','E11.47','E03.47','E78.43','I48','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('040','R07.128','E11.48','E03.48','E78.44','I49','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('041','R07.129','E11.49','E03.49','E78.45','I50','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('042','R07.130','E11.50','E03.50','E78.46','I51','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('043','R07.131','E11.51','E03.51','E78.47','I52','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('044','R07.132','E11.52','E03.52','E78.48','I53','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('045','R07.133','E11.53','E03.53','E78.49','I54','Y','Y','Y','N','N')
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5) values('046','R07.134','E11.54','E03.54','E78.50','I55','Y','Y','Y','N','N')
I will start by saying I am confused. If the #T table is your source data why are you using dynamic SQL, the data appears to be in the form you already want it you just have to order the columns the way you want them in your select statement. Or am I really missing the actual question?
March 22, 2018 at 7:21 am
I don't have an option to limit the number of diags. I pleaded that case a while ago.
So, with the help of you folks on a separate post, I was able to come up with a solution to pull the source hospital data into a pivot table. Now, I'm trying to start with that pivot table and dynamically get diag1, poa1, diag2, poa2, etc.
I realize there may be a better solution but, I don't have the skill set or knowledge to pull this off.
Below is part of the stored procedure. I end up with a couple of variables that each hold the diag codes, @columns, and another that holds the poa value ( Present On Admission), @POAColumns. The requirement is to have the diag codes "match up" with the poa value.
declare @sql nvarchar(max); -- Declare a variable to hold the Dynamic SQL to be executed
declare @columns nvarchar(max); -- Declare a variable to hold the list of columns - [Diag1], [Diag2], etc
set @columns = N''; -- Initialise the @columns variable. This is required for the next statement to be able to work. Else it will be NULL
with cteDiagUrn
as
(
-- This cte returns a unique list of DiagnosisUrnID to convert to Fields - , p.[Diag1], p.[Diag2] ...
select t1.DiagnosisUrnID
from #T1 t1
group by t1.DiagnosisUrnID
)
-- Turn the results from the cte into a CSV list of fields
select @columns += N', p.' + quotename('Diag'+convert(varchar(10), cte.DiagnosisUrnID))
from cteDiagUrn as cte
order by cte.DiagnosisUrnID;
set @columns = stuff(@columns, 1, 2, ''); -- Remove the extra ", " at the from of the list
--Create DSQL to execute
set @sql = N'
selectp.facility_id,
p.account,
p.mrn,
p.patient_name,
p.admit_date,
p.discharge_date,
p.dob,
p.sex,
p.encounter_type_cat,
p.attending_md,
p.dd,
p.payor,
p.total_charges,
p.coder,
p.ms_drg,
p.apr_drg,
p.soi,
p.rom,
p.DiagCount,
p.ProcedureCount, ' + @columns + '
into SigHealth.dbo.StreamlineHealth_Diags
from
(
selectt1.facility_id,
t1.account,
t1.mrn,
t1.patient_name,
t1.admit_date,
t1.discharge_date,
t1.dob,
t1.sex,
t1.encounter_type_cat,
t1.attending_md,
t1.dd,
t1.payor,
t1.total_charges,
t1.coder,
t1.ms_drg,
t1.apr_drg,
t1.soi,
t1.rom,
t1.DiagCount,
t1.ProcedureCount,
t1.DiagnosisCode_MisDxID,
DiagnosisUrnID = ''Diag''+convert(varchar(10), t1.DiagnosisUrnID)
from #T1 t1
) AS j
PIVOT
(
max(DiagnosisCode_MisDxID) FOR DiagnosisUrnID IN(' + replace(@columns, 'p.[', '[') + ')
) AS p;';
--exec sp_executesql @sql;
--POA------------------------------------------------------------------------------------------------------------------------------------------------
declare @POASql nvarchar(max);
declare @POAColumns nvarchar(max);
set @POAColumns = N'';
;with cteDiagUrn_poa
as
(
select t1.DiagnosisUrnID
from #T1 t1
group by t1.DiagnosisUrnID
)
select @POAColumns += N', p.' + quotename('poa'+convert(varchar(10), cte.DiagnosisUrnID))
from cteDiagUrn_poa cte
order by cte.DiagnosisUrnID;
set @POAColumns = stuff(@POAColumns, 1, 2, '');
--select * from #T1
set @POASql = N'
selectp.account, ' + @POAColumns + '
into SigHealth.dbo.StreamlineHealth_POA
from
(
selectt1.account,
t1.poa,
DiagnosisUrnID = ''poa''+convert(varchar(10), t1.DiagnosisUrnID)
from #T1 t1
) AS j
PIVOT
(
max(poa) FOR DiagnosisUrnID IN(' + replace(@POAColumns, 'p.[', '[') + ')
) AS p;';
exec sp_executesql @POASql; -- exec the DSQL.
March 22, 2018 at 7:28 am
NineIron - Thursday, March 22, 2018 7:21 AMI don't have an option to limit the number of diags. I pleaded that case a while ago.
So, with the help of you folks on a separate post, I was able to come up with a solution to pull the source hospital data into a pivot table. Now, I'm trying to start with that pivot table and dynamically get diag1, poa1, diag2, poa2, etc.
I realize there may be a better solution but, I don't have the skill set or knowledge to pull this off.
Below is part of the stored procedure. I end up with a couple of variables that each hold the diag codes, @columns, and another that holds the poa value ( Present On Admission), @POAColumns. The requirement is to have the diag codes "match up" with the poa value.
declare @sql nvarchar(max); -- Declare a variable to hold the Dynamic SQL to be executed
declare @columns nvarchar(max); -- Declare a variable to hold the list of columns - [Diag1], [Diag2], etc
set @columns = N''; -- Initialise the @columns variable. This is required for the next statement to be able to work. Else it will be NULL
with cteDiagUrn
as
(
-- This cte returns a unique list of DiagnosisUrnID to convert to Fields - , p.[Diag1], p.[Diag2] ...
select t1.DiagnosisUrnID
from #T1 t1
group by t1.DiagnosisUrnID
)
-- Turn the results from the cte into a CSV list of fields
select @columns += N', p.' + quotename('Diag'+convert(varchar(10), cte.DiagnosisUrnID))
from cteDiagUrn as cte
order by cte.DiagnosisUrnID;
set @columns = stuff(@columns, 1, 2, ''); -- Remove the extra ", " at the from of the list
--Create DSQL to execute
set @sql = N'
selectp.facility_id,
p.account,
p.mrn,
p.patient_name,
p.admit_date,
p.discharge_date,
p.dob,
p.sex,
p.encounter_type_cat,
p.attending_md,
p.dd,
p.payor,
p.total_charges,
p.coder,
p.ms_drg,
p.apr_drg,
p.soi,
p.rom,
p.DiagCount,
p.ProcedureCount, ' + @columns + '
into SigHealth.dbo.StreamlineHealth_Diags
from
(
selectt1.facility_id,
t1.account,
t1.mrn,
t1.patient_name,
t1.admit_date,
t1.discharge_date,
t1.dob,
t1.sex,
t1.encounter_type_cat,
t1.attending_md,
t1.dd,
t1.payor,
t1.total_charges,
t1.coder,
t1.ms_drg,
t1.apr_drg,
t1.soi,
t1.rom,
t1.DiagCount,
t1.ProcedureCount,
t1.DiagnosisCode_MisDxID,
DiagnosisUrnID = ''Diag''+convert(varchar(10), t1.DiagnosisUrnID)
from #T1 t1
) AS j
PIVOT
(
max(DiagnosisCode_MisDxID) FOR DiagnosisUrnID IN(' + replace(@columns, 'p.[', '[') + ')
) AS p;';
--exec sp_executesql @sql;
--POA------------------------------------------------------------------------------------------------------------------------------------------------
declare @POASql nvarchar(max);
declare @POAColumns nvarchar(max);
set @POAColumns = N'';
;with cteDiagUrn_poa
as
(
select t1.DiagnosisUrnID
from #T1 t1
group by t1.DiagnosisUrnID
)
select @POAColumns += N', p.' + quotename('poa'+convert(varchar(10), cte.DiagnosisUrnID))
from cteDiagUrn_poa cte
order by cte.DiagnosisUrnID;
set @POAColumns = stuff(@POAColumns, 1, 2, '');
--select * from #T1
set @POASql = N'
selectp.account, ' + @POAColumns + '
into SigHealth.dbo.StreamlineHealth_POA
from
(
selectt1.account,
t1.poa,
DiagnosisUrnID = ''poa''+convert(varchar(10), t1.DiagnosisUrnID)
from #T1 t1
) AS j
PIVOT
(
max(poa) FOR DiagnosisUrnID IN(' + replace(@POAColumns, 'p.[', '[') + ')
) AS p;';
exec sp_executesql @POASql; -- exec the DSQL.
Okay, so you have already pivoted the data and now just need to reorganize the columns? Is that correct?
March 22, 2018 at 7:40 am
Yes. That's correct.
March 22, 2018 at 7:45 am
NineIron - Thursday, March 22, 2018 7:40 AMYes. That's correct.
One, that means the table has as many columns needed for all the static data plus diag and poa columns for the maximum number of such pairs, correct?
If so, is the temp table in your original post an example of what that table looks like?
March 22, 2018 at 7:51 am
The sample data only includes the diags and poa. It does not include all the demographic data. I think I can piece things together, if I can get a solution to rearrange the columns.
There will always be a poa for every diag and vice versa.
March 22, 2018 at 7:55 am
NineIron - Thursday, March 22, 2018 7:51 AMThe sample data only includes the diags and poa. It does not include all the demographic data. I think I can piece things together, if I can get a solution to rearrange the columns.
There will always be a poa for every diag and vice versa.
The "dynamic" part of this is the diag/poa columns, correct? They are always named diagN and poaN where N is a numeric value (1,2,3,4,5,6,7,8,9,10,...,100,...), correct?
March 22, 2018 at 8:01 am
Yes.
March 22, 2018 at 8:32 am
NineIron - Thursday, March 22, 2018 8:01 AMYes.
March 22, 2018 at 8:36 am
Hope this can get you started:
create table #T(
ID varchar(3),
diag1 varchar(10),
diag2 varchar(10),
diag3 varchar(10),
diag4 varchar(10),
diag5 varchar(10),
poa1 varchar(10),
poa2 varchar(10),
poa3 varchar(10),
poa4 varchar(10),
poa5 varchar(10),
);
insert into #T(ID, diag1, diag2,diag3,diag4,diag5,poa1,poa2,poa3,poa4,poa5)
VALUES
('001','R07.89','E11.9','E03.9','E78.5','I10','Y','Y','Y','N','N')
,('002','R07.90','E11.10','E03.10','E78.6','I11','Y','Y','Y','N','N')
,('003','R07.91','E11.11','E03.11','E78.7','I12','Y','N','Y','N','N')
,('004','R07.92','E11.12','E03.12','E78.8','I13','Y','N','Y','N','N')
,('005','R07.93','E11.13','E03.13','E78.9','I14','Y','N','Y','N','N')
,('006','R07.94','E11.14','E03.14','E78.10','I15','Y','N','Y','N','N')
,('007','R07.95','E11.15','E03.15','E78.11','I16','Y','N','Y','N','N')
,('008','R07.96','E11.16','E03.16','E78.12','I17','Y','N','Y','N','N')
,('009','R07.97','E11.17','E03.17','E78.13','I18','Y','N','Y','N','N')
,('010','R07.98','E11.18','E03.18','E78.14','I19','Y','N','Y','N','N')
,('011','R07.99','E11.19','E03.19','E78.15','I20','Y','N','Y','N','N')
,('012','R07.100','E11.20','E03.20','E78.16','I21','E','N','Y','N','N')
,('013','R07.101','E11.21','E03.21','E78.17','I22','E','N','Y','N','N')
,('014','R07.102','E11.22','E03.22','E78.18','I23','E','N','Y','N','N')
,('015','R07.103','E11.23','E03.23','E78.19','I24','E','N','Y','N','N')
,('016','R07.104','E11.24','E03.24','E78.20','I25','E','N','Y','N','N')
,('017','R07.105','E11.25','E03.25','E78.21','I26','E','N','Y','N','N')
,('018','R07.106','E11.26','E03.26','E78.22','I27','E','Y','Y','N','N')
,('019','R07.107','E11.27','E03.27','E78.23','I28','E','Y','Y','N','N')
,('020','R07.108','E11.28','E03.28','E78.24','I29','E','Y','Y','N','N')
,('021','R07.109','E11.29','E03.29','E78.25','I30','E','Y','Y','N','N')
,('022','R07.110','E11.30','E03.30','E78.26','I31','E','Y','Y','N','N')
,('023','R07.111','E11.31','E03.31','E78.27','I32','E','Y','Y','N','N')
,('024','R07.112','E11.32','E03.32','E78.28','I33','E','Y','Y','N','N')
,('025','R07.113','E11.33','E03.33','E78.29','I34','E','Y','Y','N','N')
,('026','R07.114','E11.34','E03.34','E78.30','I35','E','Y','Y','N','N')
,('027','R07.115','E11.35','E03.35','E78.31','I36','E','Y','Y','N','N')
,('028','R07.116','E11.36','E03.36','E78.32','I37','E','Y','Y','N','N')
,('029','R07.117','E11.37','E03.37','E78.33','I38','E','Y','Y','N','N')
,('030','R07.118','E11.38','E03.38','E78.34','I39','E','Y','Y','N','N')
,('031','R07.119','E11.39','E03.39','E78.35','I40','E','Y','Y','N','N')
,('032','R07.120','E11.40','E03.40','E78.36','I41','E','Y','Y','N','N')
,('033','R07.121','E11.41','E03.41','E78.37','I42','Y','Y','Y','N','N')
,('034','R07.122','E11.42','E03.42','E78.38','I43','Y','Y','Y','N','N')
,('035','R07.123','E11.43','E03.43','E78.39','I44','Y','Y','Y','N','N')
,('036','R07.124','E11.44','E03.44','E78.40','I45','Y','Y','Y','N','N')
,('037','R07.125','E11.45','E03.45','E78.41','I46','Y','Y','Y','N','N')
,('038','R07.126','E11.46','E03.46','E78.42','I47','Y','Y','Y','N','N')
,('039','R07.127','E11.47','E03.47','E78.43','I48','Y','Y','Y','N','N')
,('040','R07.128','E11.48','E03.48','E78.44','I49','Y','Y','Y','N','N')
,('041','R07.129','E11.49','E03.49','E78.45','I50','Y','Y','Y','N','N')
,('042','R07.130','E11.50','E03.50','E78.46','I51','Y','Y','Y','N','N')
,('043','R07.131','E11.51','E03.51','E78.47','I52','Y','Y','Y','N','N')
,('044','R07.132','E11.52','E03.52','E78.48','I53','Y','Y','Y','N','N')
,('045','R07.133','E11.53','E03.53','E78.49','I54','Y','Y','Y','N','N')
,('046','R07.134','E11.54','E03.54','E78.50','I55','Y','Y','Y','N','N');
GO
DECLARE @DiagPoaColumns NVARCHAR(max), @StaticColumns NVARCHAR(MAX);
WITH BaseDynamicColumns (
ColumnName,
ColumnNameSegment1,
ColumnNameSegment2
) AS (
SELECT
col.[name]
, LEFT([col].[name], PATINDEX('%[0-9]%',[col].[name]) - 1)
, CAST(RIGHT([col].[name], LEN([col].[name]) - (PATINDEX('%[0-9]%',[col].[name]) - 1)) AS INT)
FROM
[tempdb].[sys].[tables] AS [tab]
INNER JOIN [tempdb].[sys].[columns] AS [col]
ON [col].[object_id] = [tab].[object_id]
WHERE
[tab].[name] LIKE '#T%'
AND ([col].[name] LIKE 'diag%' OR [col].[name] LIKE 'poa%')
), BaseNonDynamicColumns (
ColumnName,
ColumnId
) AS (
SELECT
col.[name]
,[col].[column_id]
FROM
[tempdb].[sys].[tables] AS [tab]
INNER JOIN [tempdb].[sys].[columns] AS [col]
ON [col].[object_id] = [tab].[object_id]
WHERE
[tab].[name] LIKE '#T%'
AND NOT([col].[name] LIKE 'diag%' OR [col].[name] LIKE 'poa%')
)
SELECT
@StaticColumns = STUFF((SELECT N',' + [bndc].[ColumnName]
FROM
[BaseNonDynamicColumns] AS [bndc]
ORDER BY
[bndc].[ColumnId]
FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,1,'')
, @DiagPoaColumns = STUFF((SELECT N',' + [bdc].[ColumnName]
FROM
[BaseDynamicColumns] AS [bdc]
ORDER BY
[bdc].[ColumnNameSegment2]
, [bdc].[ColumnNameSegment1]
FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,0,'')
SELECT @StaticColumns, @DiagPoaColumns
GO
drop table #T;
GO
March 22, 2018 at 8:55 am
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the nvarchar value 'poa' to data type int.
March 22, 2018 at 9:07 am
NineIron - Thursday, March 22, 2018 8:55 AMMsg 245, Level 16, State 1, Line 4
Conversion failed when converting the nvarchar value 'poa' to data type int.
Code runs on my machine. Posting the above error message doesn't tell us anything about what you are doing.
March 22, 2018 at 9:16 am
Sorry. I copied, pasted and ran your code. This error popped up.
I ran the query inside the CTE like this................
SELECT
col.[name]
--LEFT([col].[name],PATINDEX('%[0-9]%',[col].[name]) - 1),
--CAST(RIGHT([col].[name], LEN([col].[name]) - (PATINDEX('%[0-9]%',[col].[name]) - 1)) AS INT)
FROM [tempdb].[sys].[tables] AS [tab]
INNER JOIN [tempdb].[sys].[columns] AS [col]
ON [col].[object_id] = [tab].[object_id]
WHERE [tab].[name] LIKE '#T%'
AND ([col].[name] LIKE 'diag%' OR [col].[name] LIKE 'poa%')
and got ................
Looks like an extra poa and maybe that's why it choked on coverting something to an int?
name
diag1
diag2
diag3
diag4
diag5
poa1
poa2
poa3
poa4
poa5
poa
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply