Using dynamic sql, how can I rearrange columns

  • 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')

  • 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

  • NineIron - Thursday, March 22, 2018 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')

    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?

  • 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.

  • NineIron - Thursday, March 22, 2018 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.

    Okay, so you have already pivoted the data and now just need to reorganize the columns?  Is that correct?

  • Yes. That's correct.

  • NineIron - Thursday, March 22, 2018 7:40 AM

    Yes. 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?

  • 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.

  • NineIron - Thursday, March 22, 2018 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.

    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?

  • Yes.

  • NineIron - Thursday, March 22, 2018 8:01 AM

    Yes.

    Edit: Formatting issues, hang on.
  • 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

  • Msg 245, Level 16, State 1, Line 4

    Conversion failed when converting the nvarchar value 'poa' to data type int.

  • NineIron - Thursday, March 22, 2018 8:55 AM

    Msg 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.

  • 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