• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

SQL Hierarchical Query - Get Last Two Columns Populated

I have a table that represents ten levels of parent/child relationships.

I need to be able to identify the parent/child by retriving the last two columns of populated data in each row.  How can this be achieved using SQL Server2008
hier.jpg
0
rsaphier
Asked:
rsaphier
  • 3
1 Solution
 
John_BonCommented:
CASE Statement
0
 
John_BonCommented:

SELECT
	CASE WHEN ( L2Code IS NULL ) AND ( L3Code IS NULL ) 
		AND ( L4Code IS NULL ) AND ( L5Code IS NULL ) 
		AND ( L6Code IS NULL ) AND ( L7Code IS NULL )
		AND ( L8Code IS NULL ) AND ( L9Code IS NULL ) 
		AND ( L10Code IS NULL )
		THEN L0Code

		WHEN ( L3Code IS NULL ) AND ( L4Code IS NULL )
		AND ( L5Code IS NULL ) AND ( L6Code IS NULL ) 
		AND ( L7Code IS NULL ) AND ( L8Code IS NULL ) 
		AND ( L9Code IS NULL ) AND ( L10Code IS NULL )
		THEN L1Code
		
		WHEN ( L4Code IS NULL ) AND ( L5Code IS NULL ) 
		AND ( L6Code IS NULL ) AND ( L7Code IS NULL )
		AND ( L8Code IS NULL ) AND ( L9Code IS NULL ) 
		AND ( L10Code IS NULL )
		THEN L2Code
		
		WHEN ( L5Code IS NULL ) AND ( L6Code IS NULL ) 
		AND ( L7Code IS NULL ) AND ( L8Code IS NULL ) 
		AND ( L9Code IS NULL ) AND ( L10Code IS NULL )
		THEN L3Code
		
		--  Continue same pattern here
		
		END AS Parent

, 	CASE WHEN ( L2Code IS NULL ) AND ( L3Code IS NULL ) 
		AND ( L4Code IS NULL ) AND ( L5Code IS NULL ) 
		AND ( L6Code IS NULL ) AND ( L7Code IS NULL )
		AND ( L8Code IS NULL ) AND ( L9Code IS NULL ) 
		AND ( L10Code IS NULL )
		THEN L1Code

		WHEN ( L3Code IS NULL ) AND ( L4Code IS NULL )
		AND ( L5Code IS NULL ) AND ( L6Code IS NULL ) 
		AND ( L7Code IS NULL ) AND ( L8Code IS NULL ) 
		AND ( L9Code IS NULL ) AND ( L10Code IS NULL )
		THEN L2Code
		
		WHEN ( L4Code IS NULL ) AND ( L5Code IS NULL ) 
		AND ( L6Code IS NULL ) AND ( L7Code IS NULL )
		AND ( L8Code IS NULL ) AND ( L9Code IS NULL ) 
		AND ( L10Code IS NULL )
		THEN L3Code
		
		WHEN ( L5Code IS NULL ) AND ( L6Code IS NULL ) 
		AND ( L7Code IS NULL ) AND ( L8Code IS NULL ) 
		AND ( L9Code IS NULL ) AND ( L10Code IS NULL )
		THEN L4Code
		
		--  Continue same pattern here
		
		END AS Child
FROM dbo.hier

Open in new window

0
 
John_BonCommented:
Please try the following code...  
Hope this helps.
SELECT
	CASE WHEN ( L2Code IS NULL ) AND ( L3Code IS NULL ) 
		AND ( L4Code IS NULL ) AND ( L5Code IS NULL ) 
		AND ( L6Code IS NULL ) AND ( L7Code IS NULL )
		AND ( L8Code IS NULL ) AND ( L9Code IS NULL ) 
		AND ( L10Code IS NULL )
		THEN L0Code

		WHEN ( L3Code IS NULL ) AND ( L4Code IS NULL )
		AND ( L5Code IS NULL ) AND ( L6Code IS NULL ) 
		AND ( L7Code IS NULL ) AND ( L8Code IS NULL ) 
		AND ( L9Code IS NULL ) AND ( L10Code IS NULL )
		THEN L1Code
		
		WHEN ( L4Code IS NULL ) AND ( L5Code IS NULL ) 
		AND ( L6Code IS NULL ) AND ( L7Code IS NULL )
		AND ( L8Code IS NULL ) AND ( L9Code IS NULL ) 
		AND ( L10Code IS NULL )
		THEN L2Code
		
		WHEN ( L5Code IS NULL ) AND ( L6Code IS NULL ) 
		AND ( L7Code IS NULL ) AND ( L8Code IS NULL ) 
		AND ( L9Code IS NULL ) AND ( L10Code IS NULL )
		THEN L3Code
		
		WHEN ( L6Code IS NULL ) 
		AND ( L7Code IS NULL ) AND ( L8Code IS NULL ) 
		AND ( L9Code IS NULL ) AND ( L10Code IS NULL )
		THEN L4Code		

		WHEN ( L7Code IS NULL ) AND ( L8Code IS NULL ) 
		AND ( L9Code IS NULL ) AND ( L10Code IS NULL )
		THEN L5Code		

		WHEN ( L8Code IS NULL ) 
		AND ( L9Code IS NULL ) AND ( L10Code IS NULL )
		THEN L6Code		

		WHEN ( L9Code IS NULL ) AND ( L10Code IS NULL )
		THEN L7Code		

		WHEN ( L10Code IS NULL )
		THEN L8Code		

		ELSE
			L9Code

		END AS Parent

, 	CASE WHEN ( L2Code IS NULL ) AND ( L3Code IS NULL ) 
		AND ( L4Code IS NULL ) AND ( L5Code IS NULL ) 
		AND ( L6Code IS NULL ) AND ( L7Code IS NULL )
		AND ( L8Code IS NULL ) AND ( L9Code IS NULL ) 
		AND ( L10Code IS NULL )
		THEN L1Code

		WHEN ( L3Code IS NULL ) AND ( L4Code IS NULL )
		AND ( L5Code IS NULL ) AND ( L6Code IS NULL ) 
		AND ( L7Code IS NULL ) AND ( L8Code IS NULL ) 
		AND ( L9Code IS NULL ) AND ( L10Code IS NULL )
		THEN L2Code
		
		WHEN ( L4Code IS NULL ) AND ( L5Code IS NULL ) 
		AND ( L6Code IS NULL ) AND ( L7Code IS NULL )
		AND ( L8Code IS NULL ) AND ( L9Code IS NULL ) 
		AND ( L10Code IS NULL )
		THEN L3Code
		
		WHEN ( L5Code IS NULL ) AND ( L6Code IS NULL ) 
		AND ( L7Code IS NULL ) AND ( L8Code IS NULL ) 
		AND ( L9Code IS NULL ) AND ( L10Code IS NULL )
		THEN L4Code
		
		WHEN ( L6Code IS NULL ) 
		AND ( L7Code IS NULL ) AND ( L8Code IS NULL ) 
		AND ( L9Code IS NULL ) AND ( L10Code IS NULL )
		THEN L5Code		

		WHEN ( L7Code IS NULL ) AND ( L8Code IS NULL ) 
		AND ( L9Code IS NULL ) AND ( L10Code IS NULL )
		THEN L6Code		

		WHEN ( L8Code IS NULL ) 
		AND ( L9Code IS NULL ) AND ( L10Code IS NULL )
		THEN L7Code		

		WHEN ( L9Code IS NULL ) AND ( L10Code IS NULL )
		THEN L8Code		

		WHEN ( L10Code IS NULL )
		THEN L9Code		

		ELSE
			L10Code
		
		END AS Child
FROM dbo.hier

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now