SQLServerCentral » T-SQL (SS2K8) » Sequential numericInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralTue, 17 Jan 2017 03:06:10 GMT20Sequential numerichttps://www.sqlservercentral.com/Forums/FindPost1470869.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.
TABLE1
seq_no seq_nbr dx_code
E3CD8342-1294-4CBA-9201-D51C07E9FB0C 1 366.16
997312BA-8C90-4773-B0FC-1838C46A4728 3 370.03
5DC781A2-71BC-4148-9D56-DA95D3F8F081 4 362.52
E65354B3-F404-430B-8153-EDD7D1921431 4 362.52
TABLE2
dx_code1 dx_code2 dx_code3 dx_code4
366.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'
GO
INSERT INTO Table1 (seq_no,seq_nbr,dx_code)
SELECT NEWID(),'3','370.03'
GO
INSERT INTO Table1 (seq_no,seq_nbr,dx_code)
SELECT NEWID(),'4','362.52'
GO
INSERT 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_code
FROM(SELECT dx_code1,dx_code2,dx_code3,dx_code4
FROM Table2 ) d
UNPIVOT
(dx_code FOR sequence IN (dx_code1,dx_code2,dx_code3,dx_code4)
)AS unpvt_assess
select * from #diag_codes
DROP TABLE #diag_codes
[/code]
//Edit
Hit 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.
Mon, 08 Jul 2013 12:41:34 GMTkcd7216RE: Sequential numerichttps://www.sqlservercentral.com/Forums/FindPost1471322.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 numerichttps://www.sqlservercentral.com/Forums/FindPost1471304.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 numerichttps://www.sqlservercentral.com/Forums/FindPost1471055.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 #TempTable
IF 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_code
FROM(SELECT seq_nbr, dx_code1,dx_code2,dx_code3,dx_code4
FROM #TempTable ) d
UNPIVOT
(dx_code FOR sequence IN (dx_code1,dx_code2,dx_code3,dx_code4)
)AS unpvt_assess
SELECT
sequence
,dx_code
FROM
#diag_codes as d
WHERE
rownum = 1
ORDER BY
sequence
[/code]
Mon, 08 Jul 2013 00:59:46 GMTSteven WillisRE: Sequential numerichttps://www.sqlservercentral.com/Forums/FindPost1471009.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.c