July 5, 2013 at 1:52 pm
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.
TABLE1
seq_no seq_nbr dx_code
E3CD8342-1294-4CBA-9201-D51C07E9FB0C1366.16
997312BA-8C90-4773-B0FC-1838C46A47283370.03
5DC781A2-71BC-4148-9D56-DA95D3F8F0814362.52
E65354B3-F404-430B-8153-EDD7D19214314362.52
TABLE2
dx_code1dx_code2dx_code3dx_code4
366.16362.52370.03362.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'
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
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_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
//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.
July 7, 2013 at 6:57 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 8, 2013 at 12:59 am
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 #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
July 8, 2013 at 11:44 am
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.
July 8, 2013 at 12:41 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply