P1, Sql Server - rextester

P1

CREATE TABLE Table1
    ("claim_number" int, "record_type" varchar(1), "claim_type_code" varchar(1), "received_date" datetime, "billing_pr_npi" varchar(10), "referring_pr_npi" varchar(10), "facility_npi" varchar(10), "primary_diagnosis" varchar(5), "diagnosis_code_2" varchar(5), "diagnosis_code_3" varchar(5), "diagnosis_code_4" varchar(5), "diagnosis_code_5" varchar(5), "diagnosis_code_6" varchar(5), "diagnosis_code_7" varchar(5), "diagnosis_code_8" varchar(5))
;
    
INSERT INTO Table1
    ("claim_number", "record_type", "claim_type_code", "received_date", "billing_pr_npi", "referring_pr_npi", "facility_npi", "primary_diagnosis", "diagnosis_code_2", "diagnosis_code_3", "diagnosis_code_4", "diagnosis_code_5", "diagnosis_code_6", "diagnosis_code_7", "diagnosis_code_8")
VALUES
    (1, 'C', 'P', '2014-04-01 00:00:00', 1225058019, 1982608568, 1225058019, '37200', '4659', NULL, NULL, NULL, NULL, NULL, NULL),
    (2, 'C', 'P', '2014-04-01 00:00:00', 1871538769, 1457635740, 1871538769, 'V202', 'V0481', NULL, NULL, NULL, NULL, NULL, NULL),
    (3, 'C', 'P', '2014-04-01 00:00:00', 1003961251, NULL, 1003961251, '31400', '78052', 4779, 56400, NULL, NULL, NULL, NULL),
    (4, 'C', 'P', '2014-04-01 00:00:00', 1336263946, 1922101963, 1336263946, '73730', NULL, NULL, NULL, NULL, NULL, NULL, NULL),
    (5, 'C', 'P', '2014-04-01 00:00:00', 1922124866, NULL, 1134122179, '7590', NULL, NULL, NULL, NULL, NULL, NULL, NULL),
    (6, 'C', 'P', '2014-04-01 00:00:00', 1013950807, 1780788919, 1013950807, 'V0481', NULL, NULL, NULL, NULL, NULL, NULL, NULL),
    (7, 'C', 'P', '2014-04-01 00:00:00', 1316914385, 1932280583, 1316914385, '72781', '84212', NULL, NULL, NULL, NULL, NULL, NULL),
    (8, 'C', 'P', '2014-04-01 00:00:00', 1946852486, NULL, 1468726842, '4682', '94852', 3180, 76815, NULL, NULL, NULL, NULL),
    (9, 'C', 'P', '2014-04-01 00:00:00', 1316914385, 1922067578, 1093783763, '84500', NULL, NULL, NULL, NULL, NULL, NULL, NULL),
    (10, 'C', 'P', '2014-04-01 00:00:00', 1063502839, 1235128919, 1003961251, '4371', '51900', NULL, NULL, NULL, NULL, NULL, NULL)
;

select * from Table1;

SELECT claim_number, record_type, claim_type_code, received_date, billing_pr_npi, referring_pr_npi, facility_npi, diagnosis_code
FROM (
        SELECT claim_number,
             record_type,
             claim_type_code, 
             received_date, 
             billing_pr_npi,
             referring_pr_npi,
             facility_npi,
             primary_diagnosis, 
             diagnosis_code_2, 
             diagnosis_code_3, 
             diagnosis_code_4, 
             diagnosis_code_5, 
             diagnosis_code_6, 
             diagnosis_code_7, 
             diagnosis_code_8 
        FROM Table1
      ) tbl1
        UNPIVOT ( diagnosis_code for x in 
                   (
                     primary_diagnosis, 
                     diagnosis_code_2, 
                     diagnosis_code_3, 
                     diagnosis_code_4, 
                     diagnosis_code_5, 
                     diagnosis_code_6, 
                     diagnosis_code_7, 
                     diagnosis_code_8
                   )
                ) as y


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development