SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Groupin
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kalesh
Starting Member

India
1 Posts

Posted - 04/07/2014 :  13:01:39  Show Profile  Reply with Quote
table 1



id country parentid leveldepth
-- ----- ------ ---------
1 india null 1

2 aus null 1

3 ap 1 2


4 up 1 2


5 perth 2 2


6 khammam 1 3



table 2


id seats
--- ---------
6 10

2 5



final result
------------



if i pass the value 1 it has to display like this.Is it possible

c1 c2 c3 c4
---- ----- ----- ---------

india null null null


india ap null null


india ap khammam 6


india up null null




visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/13/2014 :  14:45:49  Show Profile  Reply with Quote
What you need is a recursive CTE.
see
http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

India
107 Posts

Posted - 04/14/2014 :  06:14:23  Show Profile  Reply with Quote
Can anyone suggest me the best way ..I am sure it's not a perfect way to use Recursive CTE


CREATE  TABLE #Table1 (ID INT,Country VARCHAR(100),ParentID INT,LevelDepth INT)
CREATE  TABLE #Table2 (ID INT,Seats INT)
INSERT INTO #Table1 VALUES (1,'India',NULL,1),(2,'aus',NULL,1),(3,'ap',1,2),(4,'up',1,2),(5,'perth',2,2),(6,'Khammam',1,3)
INSERT INTO #Table2 VALUES (6,10),(2,5)
DECLARE @ID INT =1
;WITH Country (C1,C2,C3,C4)
AS
(	SELECT Country,NULL,NULL,NULL FROM #Table1 T1 WHERE T1.ParentID IS NULL  AND T1.ID =@ID
	UNION  ALL
	SELECT T1.Country,T2.Country,NULL,NULL FROM #Table1 T1 INNER  JOIN #Table1 T2 ON T1.ID = T2.ParentID WHERE T2.ID NOT IN (Select ID FROM #Table2) AND T1.ID =@ID
	UNION ALL
	SELECT T1.Country,T2.Country,T3.Country,T3.ID FROM #Table1 T1 INNER JOIN #Table1 T2 ON T1.ID = T2.ParentID  LEFT JOIN #Table1 T3 ON T2.ID =T3.LevelDepth 
	WHERE T2.ID NOT IN (Select ID FROM #Table2) AND T1.ID =@ID
)
SELECT DISTINCT * FROM Country


---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

India
107 Posts

Posted - 04/16/2014 :  05:18:43  Show Profile  Reply with Quote
Still not sure about this approach ....Need a help .......


CREATE  TABLE #Table1 (ID INT,Region VARCHAR(100),ParentID INT,LevelDepth INT)
INSERT INTO #Table1 VALUES (1,'India',NULL,1),(2,'Australia',NULL,1),(3,'AndhraPradesh',1,2),(4,'UttarPradesh',1,2),(5,'Perth',2,2),(6,'Krishna',3,1)
DECLARE @ID INT =1
;WITH CTE (ID,ParentID,SortKey,Country,State,District )
AS
(
  SELECT     ID
	    ,ParentID
	    ,CONVERT(nvarchar(1024), Region) AS SortKey
            ,CONVERT(nvarchar(1024), Region) AS Country
            ,CONVERT(nvarchar(1024), NULL) AS State
            ,CONVERT(nvarchar(1024), NULL) AS District
 FROM	     #Table1 WHERE ParentID IS NULL AND ID = @ID
 UNION ALL 
 SELECT	     Children.ID
	    ,Children.ParentID
	    ,CONVERT(NVARCHAR(1024), RTRIM(Parents.SortKey) + ' -->' + Children.Region) AS SortKey 
	    ,CASE WHEN CHARINDEX(' -->',Parents.SortKey) = 0 THEN Parents.SortKey ELSE    LEFT(Parents.SortKey,CHARINDEX(' -->',Parents.SortKey))END AS Country  
			,CASE WHEN  Children.ParentID IN (SELECT ID FROM #Table1 WHERE ParentID IS NULL) THEN Children.Region ELSE RIGHT(Parents.SortKey,LEN(Parents.SortKey)-CHARINDEX(' -->',Parents.SortKey)-3) END AS State  
	    ,CASE WHEN CHARINDEX(' -->',Parents.SortKey) = 0 THEN NULL ELSE CONVERT(NVARCHAR(1024),Children.Region)  END AS District 
 FROM	    #Table1 AS Children 
 INNER JOIN CTE AS Parents 
 ON			Children.ParentID = Parents.ID
)
SELECT Country AS C1,State AS C2,District AS C3,(CASE WHEN District IS NOT NULL THEN ID ELSE NULL END) AS C4  FROM CTE ORDER BY State


---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Edited by - MuralikrishnaVeera on 04/16/2014 05:22:00
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000