P1, Sql Server - rextester

Language: Layout:

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

View schema
Execution time: 0,03 sec, rows selected: 30, rows affected: 10, absolute service time: 0,2 sec

	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
1	1	C	P	01.04.2014 00:00:00	1225058019	1982608568	1225058019	37200	4659	NULL	NULL	NULL	NULL	NULL	NULL
2	2	C	P	01.04.2014 00:00:00	1871538769	1457635740	1871538769	V202	V0481	NULL	NULL	NULL	NULL	NULL	NULL
3	3	C	P	01.04.2014 00:00:00	1003961251	NULL	1003961251	31400	78052	4779	56400	NULL	NULL	NULL	NULL
4	4	C	P	01.04.2014 00:00:00	1336263946	1922101963	1336263946	73730	NULL	NULL	NULL	NULL	NULL	NULL	NULL
5	5	C	P	01.04.2014 00:00:00	1922124866	NULL	1134122179	7590	NULL	NULL	NULL	NULL	NULL	NULL	NULL
6	6	C	P	01.04.2014 00:00:00	1013950807	1780788919	1013950807	V0481	NULL	NULL	NULL	NULL	NULL	NULL	NULL
7	7	C	P	01.04.2014 00:00:00	1316914385	1932280583	1316914385	72781	84212	NULL	NULL	NULL	NULL	NULL	NULL
8	8	C	P	01.04.2014 00:00:00	1946852486	NULL	1468726842	4682	94852	3180	76815	NULL	NULL	NULL	NULL
9	9	C	P	01.04.2014 00:00:00	1316914385	1922067578	1093783763	84500	NULL	NULL	NULL	NULL	NULL	NULL	NULL
10	10	C	P	01.04.2014 00:00:00	1063502839	1235128919	1003961251	4371	51900	NULL	NULL	NULL	NULL	NULL	NULL

	claim_number	record_type	claim_type_code	received_date	billing_pr_npi	referring_pr_npi	facility_npi	diagnosis_code
1	1	C	P	01.04.2014 00:00:00	1225058019	1982608568	1225058019	37200
2	1	C	P	01.04.2014 00:00:00	1225058019	1982608568	1225058019	4659
3	2	C	P	01.04.2014 00:00:00	1871538769	1457635740	1871538769	V202
4	2	C	P	01.04.2014 00:00:00	1871538769	1457635740	1871538769	V0481
5	3	C	P	01.04.2014 00:00:00	1003961251	NULL	1003961251	31400
6	3	C	P	01.04.2014 00:00:00	1003961251	NULL	1003961251	78052
7	3	C	P	01.04.2014 00:00:00	1003961251	NULL	1003961251	4779
8	3	C	P	01.04.2014 00:00:00	1003961251	NULL	1003961251	56400
9	4	C	P	01.04.2014 00:00:00	1336263946	1922101963	1336263946	73730
10	5	C	P	01.04.2014 00:00:00	1922124866	NULL	1134122179	7590
11	6	C	P	01.04.2014 00:00:00	1013950807	1780788919	1013950807	V0481
12	7	C	P	01.04.2014 00:00:00	1316914385	1932280583	1316914385	72781
13	7	C	P	01.04.2014 00:00:00	1316914385	1932280583	1316914385	84212
14	8	C	P	01.04.2014 00:00:00	1946852486	NULL	1468726842	4682
15	8	C	P	01.04.2014 00:00:00	1946852486	NULL	1468726842	94852
16	8	C	P	01.04.2014 00:00:00	1946852486	NULL	1468726842	3180
17	8	C	P	01.04.2014 00:00:00	1946852486	NULL	1468726842	76815
18	9	C	P	01.04.2014 00:00:00	1316914385	1922067578	1093783763	84500
19	10	C	P	01.04.2014 00:00:00	1063502839	1235128919	1003961251	4371
20	10	C	P	01.04.2014 00:00:00	1063502839	1235128919	1003961251	51900