SQLServerCentral.com / SQL Server 2008 / T-SQL (SS2K8) / Sequential numeric / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 22 May 2015 04:22:08 GMT20RE: Sequential numerichttp://www.sqlservercentral.com/Forums/Topic1470869-392-1.aspxI 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.Mon, 08 Jul 2013 12:41:34 GMTSean LangeRE: Sequential numerichttp://www.sqlservercentral.com/Forums/Topic1470869-392-1.aspxMy 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.[code="sql"]SELECT * FROM Table1 t JOIN #diag_codes dc ON t.dx_code =dc.dx_code[/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.Mon, 08 Jul 2013 11:44:18 GMTkcd7216RE: Sequential numerichttp://www.sqlservercentral.com/Forums/Topic1470869-392-1.aspxTry this:[code="sql"]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[/code] Mon, 08 Jul 2013 00:59:46 GMTSteven WillisRE: Sequential numerichttp://www.sqlservercentral.com/Forums/Topic1470869-392-1.aspxI 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.Sun, 07 Jul 2013 18:57:19 GMTdwain.cSequential numerichttp://www.sqlservercentral.com/Forums/Topic1470869-392-1.aspxI 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[code="sql"]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[/code]Pivot TABLE2 [code="sql"]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[/code]//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.Fri, 05 Jul 2013 13:52:46 GMTkcd7216