Solved

SQL Hierarchical Query - Get Last Two Columns Populated

Posted on 2011-09-07
3
373 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

856 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