Solved

SQL Hierarchical Query - Get Last Two Columns Populated

Posted on 2011-09-07
3
339 Views
Last Modified: 2012-05-12
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
Comment
Question by:rsaphier
  • 3
3 Comments
 
LVL 2

Expert Comment

by:John_Bon
Comment Utility
CASE Statement
0
 
LVL 2

Expert Comment

by:John_Bon
Comment Utility

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
 
LVL 2

Accepted Solution

by:
John_Bon earned 500 total points
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now