Solved

SQL Hierarchical Query - Get Last Two Columns Populated

Posted on 2011-09-07
3
366 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
ID: 36500974
CASE Statement
0
 
LVL 2

Expert Comment

by:John_Bon
ID: 36501025

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
ID: 36501042
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

810 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