Solved

SQL Hierarchical Query - Get Last Two Columns Populated

Posted on 2011-09-07
3
389 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

624 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