Recursive SQL to fill the value of a several columns

I have a bit of a strange design requirement. Here is a example table of what I am working with

DECLARE @T TABLE
(
kID INT,
sCode VARCHAR(50),
kParentID INT NULL,
nNestLevel INT 
)

INSERT INTO @T
SELECT 10009,'Professional Fees              ',NULL  ,0 UNION ALL
SELECT 10371,'Exam Room Fees                 ',10009 ,1 UNION ALL
SELECT 10410,'Diagnostic Tests               ',NULL  ,0 UNION ALL
SELECT 10011,'Pharmacy Income                ',NULL  ,0 UNION ALL
SELECT 10395,'Dietary Products               ',NULL  ,0 UNION ALL
SELECT 10053,'Outpatient Services & Treatment',10371 ,2 UNION ALL
SELECT 10055,'Canine Vaccines                ',10371 ,2 UNION ALL
SELECT 10200,'Office Calls and Exams         ',10371 ,2 UNION ALL
SELECT 10204,'Feline Vaccines                ',10371 ,2 UNION ALL
SELECT 10205,'Ferret Vaccines                ',10371 ,2 UNION ALL
SELECT 10206,'Euthanasia                     ',10371 ,2 UNION ALL
SELECT 10207,'Cremation & Burial             ',10371 ,2 UNION ALL
SELECT 10304,'Laser Therapy                  ',10371 ,2 UNION ALL
SELECT 10379,'Hospitalization & Inpatient Ser',10371 ,2 UNION ALL
SELECT 10283,'Wellness Plan Diagnostics      ',10410 ,1 UNION ALL
SELECT 10411,'Opthalmic Tests                ',10410 ,1 UNION ALL
SELECT 10412,'Blood Pressure                 ',10410 ,1 UNION ALL
SELECT 10413,'Diagnostic Other Tests         ',10410 ,1 UNION ALL
SELECT 10414,'EKG                            ',10410 ,1 UNION ALL
SELECT 10267,'In Hospital Pharmacy           ',10011 ,1 UNION ALL
SELECT 10368,'Parasiticides                  ',10011 ,1 UNION ALL
SELECT 10383,'Outpatient Pharmacy            ',10011 ,1 UNION ALL
SELECT 10013,'Prescription Diets             ',10395 ,1 UNION ALL
SELECT 10021,'Non Prescription Diets         ',10395 ,1 UNION ALL
SELECT 10083,'Inpatient Medical Services & Tx',10379 ,3 UNION ALL
SELECT 10201,'Outpatient Treatments          ',10053 ,3 UNION ALL
SELECT 10280,'Herbal & Acupuncture Exams     ',10200 ,3 UNION ALL
SELECT 10295,'Hospital Tasks Income          ',10379 ,3 UNION ALL
SELECT 10308,'Distemper/Parvo                ',10055 ,3 UNION ALL
SELECT 10309,'Bordetella                     ',10055 ,3

Each item will point to it's parent in the next level up. What I need to make a query that is based on another table that references this one and uses kID as a foreign key, I need to add three columns to that table,

Column 1

Show the sCode of the foreign key,

Column 2

Show the the sCode of the parent of the original key that has a nNestLevel of 1 (with a exception, will get to that in a bit), if the original ID is allready at a nNestLevel of 1 or 0 it should print the same sCode again as the first column.

Column 3

Show the the sCode of the parent of the original key that has a nNestLevel of 0 (with a exception, will get to that in a bit), if the original ID is allready at a nNestLevel of 0 it should print the same sCode again as the first column.

I am开发者_运维技巧 fumbling around doing the recursive look-ups and I am beginning to understand how to do it, however the issue is there is a exception to the rules that is throwing me off:

if the root parent kID is 10009 use nNestLevel 1 and 2 instead of 0 and 1 for the rules of the last two columns.

I don't know what to do to handle that special case.

My solution

Here is the solution based off of Martin Smith's answer. A few small changes where made. The first CTE was not passing the baseID correctly. The select statement in the final query also needed to adjusted so the case statements would return their correct columns, before they had potential to return Null.

;WITH R AS
(
SELECT * , 1 AS L, kID AS BasekID
FROM Accounts
UNION ALL
SELECT T.*, L+1, R.BasekID
FROM Accounts T JOIN R ON T.kID = R.kParentID
), T AS
(
SELECT BasekID AS kID,
       MAX(CASE WHEN L=1 THEN sCode END) AS sCode,
       MAX(CASE WHEN L=1 THEN nNestLevel END) AS nNestLevel,       
       MAX(CASE WHEN nNestLevel =0 THEN sCode END) AS sCode0,
       MAX(CASE WHEN nNestLevel =1 THEN sCode  END) AS sCode1,
       MAX(CASE WHEN nNestLevel =2 THEN sCode END) AS sCode2,
       MAX(CASE WHEN nNestLevel =0 THEN kID END) AS RootkID
FROM R
GROUP BY BasekID
 )
  SELECT kID, sCode AS Col1,
        --This case statement is to handel when the rootkID is 10009, we should go up a level if it is.
    CASE WHEN nNestLevel <= 1 AND RootkID <> 10009 THEN sCode
         WHEN nNestLevel <= 2 AND RootkID = 10009 THEN sCode
         WHEN RootkID = 10009 THEN sCode2
         ELSE sCode1 END AS Col2,
    CASE WHEN nNestLevel <= 0 AND RootkID <> 10009 THEN sCode
         WHEN nNestLevel <= 1 AND RootkID = 10009 THEN sCode
         WHEN RootkID = 10009 THEN sCode1
         ELSE sCode0 END AS Col3
 FROM T

I think something like this should work. Might not be exactly right but I'll let you sort that one out!

;WITH R AS
(
SELECT * , 1 AS L, kID AS BasekID
FROM @T
UNION ALL
SELECT T.*, L+1, R.kID
FROM @T T JOIN R ON T.kID = R.kParentID
), T AS
(
SELECT BasekID AS kID,
       MAX(CASE WHEN L=1 THEN sCode END) AS sCode,
       MAX(CASE WHEN L=1 THEN nNestLevel END) AS nNestLevel,       
       MAX(CASE WHEN nNestLevel =0 THEN sCode END) AS sCode0,
       MAX(CASE WHEN nNestLevel =1 THEN sCode END) AS sCode1,
       MAX(CASE WHEN nNestLevel =2 THEN sCode END) AS sCode2,
       MAX(CASE WHEN nNestLevel =0 THEN kID END) AS RootkID
FROM R
GROUP BY BasekID
 )
  SELECT sCode AS Col1,
        CASE WHEN nNestLevel <=1 AND RootkID <> 10009 THEN sCode
             WHEN nNestLevel <=1 AND RootkID = 10009 THEN sCode2
             ELSE sCode1 END AS Col2,
        CASE WHEN nNestLevel =0 AND RootkID <> 10009 THEN sCode
             WHEN nNestLevel =0 AND RootkID = 10009 THEN sCode1
             ELSE sCode0 END AS Col3
 FROM T