Hi guys, I hope you can point me in the right direction

Transact-SQL
1

I have this sticky situation where I have a table with a parent/child relationship in which I have to match the child field information to match the parent.

DocID AttachID ProdField
1.94.2560 1 abc123
1.94.2561 1 Null
1.94.2567 6 def456
1.94.2568 6 Null
1.94.2569 6 Null
1.94.2570 6 Null
1.94.2571 6 Null

What I would like to do is to use the AttachID to recursively go down the column and update the Prodfield according to the first distinct AttachID. The output would look like this

DocID AttachID ProdField
1.94.2560 1 abc123
1.94.2561 1 abc123
1.94.2567 6 def456
1.94.2568 6 def456
1.94.2569 6 def456
1.94.2570 6 def456
1.94.2571 6 def456

My thought process is that I would have to use a combination of CTE and update or maybe a CASE statement to make this work. However, at this moment, I am just spinning my wheels and I figured some fresh eyes probably could point me in the right direction.

2

is this a self referencing table with both parent and child data. or are there other tables involved in this relationship? Is ProdField associated with the parent or with child or both?

3

Assuming that you are on a version of SQL Server that supports the OVER clause...

-- TestData
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
	DocID CHAR(9) NOT NULL PRIMARY KEY CLUSTERED,
	AttachID INT NOT NULL,
	ProdField CHAR(6) NULL
	);

INSERT #TestData (DocID, AttachID, ProdField) VALUES 
	('1.94.2560', 1, 'abc123'),
	('1.94.2561', 1, NULL),
	('1.94.2567', 6, 'def456'),
	('1.94.2568', 6, NULL),
	('1.94.2569', 6, NULL),
	('1.94.2570', 6, NULL),
	('1.94.2571', 6, NULL);

-- Create POC index... http://sqlmag.com/sql-server-2012/sql-server-2012-how-write-t-sql-window-functions-part-3
-- ( reduces query cost by ~75% by eliminating the sort operation )
CREATE NONCLUSTERED INDEX ix_TestData_AttachID ON #TestData (
	AttachID
	)
INCLUDE (
	ProdField,
	DocID
	);

--====================================================

-- Solution
SELECT 
	td.DocID, 
	td.AttachID, 
	SmearedPF = MAX(td.ProdField) OVER (PARTITION BY td.AttachID)
FROM
	#TestData td;
1 Like
4

My apologies, this is a self referencing table.

5

Thanks for pointing me in the right direction, I will let you know if I am successful.

6

Works like a charm, thanks for your help.

7

No problem. Glad to help. :slight_smile: