## Sequential numeric

 Author Message kcd7216 SSC Rookie Group: General Forum Members Points: 39 Visits: 182 I have a need to update numeric values in a table column so that they are unique and sequencial, the order of which is determined by another column in the same table, and by data in a 2nd table. I am running into a problem when there is duplicate data, and I can't figure out how to get the numeric values to be unique.For the data below, I want the seq_nbr column in TABLE1 to be in order 1,2,3,4 based on the order the codes are listed horizontally in TABLE2. TABLE1seq_no seq_nbr dx_codeE3CD8342-1294-4CBA-9201-D51C07E9FB0C 1 366.16997312BA-8C90-4773-B0FC-1838C46A4728 3 370.035DC781A2-71BC-4148-9D56-DA95D3F8F081 4 362.52E65354B3-F404-430B-8153-EDD7D1921431 4 362.52TABLE2dx_code1 dx_code2 dx_code3 dx_code4366.16 362.52 370.03 362.52`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`Pivot TABLE2 `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`//EditHit post too soon.I have the syntax above for creating the 2 tables and adding data for the test scenario.I tried some queries with the pivot of TABLE2 to get my numeric values updated, but was running into difficulty due to the duplicate dx_code values. dwain.c SSCoach Group: General Forum Members Points: 18039 Visits: 6431 I hesitate to say this but I don't think you've got enough information to solve this.Any query that might appear to solve it would need to rely on the ordering of the rows in Table1, which SQL does not guarantee. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables Steven Willis SSCrazy Group: General Forum Members Points: 2071 Visits: 1721 Try this:`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` kcd7216 SSC Rookie Group: General Forum Members Points: 39 Visits: 182 My original post may not have been very clear as to what I would like for the end result. Taking it a step farther to help illustrate the direction I am going, I did a simple join of Table1 and #diag_codes on the dx_code field.`SELECT * FROM Table1 t JOIN #diag_codes dc ON t.dx_code =dc.dx_code`Now that leaves me in a sense with two duplicate rows. The only difference being the seq_no column. I am trying to figure out if there is any logical way to remove the duplicates or prevent the duplicates from occurring, and then update the column Table1.seq_nbr with the value in the field #diag_codes.dx.I have uploaded two attachments to help illustrate what I am trying to achieve. The "join with duplicates" shows the results of the above query, where I have crossed out the duplicates that I want removed.The "desired result" attachment shows what the data in Table1 should look like after updating the seq_nbr column. Attachments join with duplicates.jpg (7 views, 74.00 KB) desired result.jpg (35 views, 37.00 KB) Sean Lange SSC Guru Group: General Forum Members Points: 63338 Visits: 17966 I have to second what Dwain already stated. You don't have any way to force the order here. Why is Row 2 correct and Row 3 not correct? How do you know what order those will appear? You don't because you have no order by, and you have nothing to use as an order by. For that matter how do you know if dx_code2 belongs to dx 2? Why can't it be dx_code4? There is nothing other than the perceived order of the unpivot to know this. If you had a normalized table for Table2 this would be not only possible, it would be simple. _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Modens splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)