CREATE TABLE Table1 (seq_no UNIQUEIDENTIFIER,seq_nbr INT,dx_code VARCHAR(6)) CREATE TABLE Table2 (dx_code1 VARCHAR(6),dx_code2 VARCHAR(6),dx_code3 VARCHAR(6),dx_code4 VARCHAR(6)) INSERT INTO Table1(seq_no,seq_nbr,dx_code) SELECT NEWID(),'1','366.16'GOINSERT INTO Table1 (seq_no,seq_nbr,dx_code)SELECT NEWID(),'3','370.03'GOINSERT INTO Table1 (seq_no,seq_nbr,dx_code) SELECT NEWID(),'4','362.52' GOINSERT INTO Table1 (seq_no,seq_nbr,dx_code) SELECT NEWID(),'4','362.52'GO INSERT INTO Table2 (dx_code1,dx_code2,dx_code3,dx_code4) VALUES('366.16','362.52','370.03','362.52') GO

CREATE TABLE #diag_codes(dx INT IDENTITY,sequence char(8),dx_code VARCHAR(6))INSERT INTO #diag_codes (sequence,dx_code)SELECT sequence,dx_codeFROM(SELECT dx_code1,dx_code2,dx_code3,dx_code4FROM Table2 ) dUNPIVOT(dx_code FOR sequence IN (dx_code1,dx_code2,dx_code3,dx_code4))AS unpvt_assessselect * from #diag_codesDROP TABLE #diag_codes

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable;CREATE TABLE #TempTable (seq_nbr INT IDENTITY(1,1), dx_code1 VARCHAR(6),dx_code2 VARCHAR(6),dx_code3 VARCHAR(6),dx_code4 VARCHAR(6)) INSERT INTO #TempTable (dx_code1,dx_code2,dx_code3,dx_code4) VALUES('366.16','362.52','370.03','362.52')--SELECT * FROM #TempTableIF OBJECT_ID('tempdb..#diag_codes') IS NOT NULL DROP TABLE #diag_codes;CREATE TABLE #diag_codes(rownum int,seq_nbr int identity(1,1),sequence char(8),dx_code VARCHAR(6))INSERT INTO #diag_codes (rownum,sequence,dx_code)SELECT ROW_NUMBER() OVER (PARTITION BY dx_code ORDER BY sequence) AS rownum, sequence, dx_codeFROM(SELECT seq_nbr, dx_code1,dx_code2,dx_code3,dx_code4FROM #TempTable ) dUNPIVOT(dx_code FOR sequence IN (dx_code1,dx_code2,dx_code3,dx_code4))AS unpvt_assessSELECT sequence ,dx_codeFROM #diag_codes as dWHERE rownum = 1ORDER BY sequence

SELECT * FROM Table1 t JOIN #diag_codes dc ON t.dx_code =dc.dx_code