Author |
Topic |
kalesh
Starting Member
1 Post |
Posted - 2014-04-07 : 13:01:39
|
table 1id country parentid leveldepth-- ----- ------ ---------1 india null 12 aus null 13 ap 1 24 up 1 25 perth 2 26 khammam 1 3table 2id seats--- ---------6 102 5final result------------if i pass the value 1 it has to display like this.Is it possiblec1 c2 c3 c4---- ----- ----- ---------india null null nullindia ap null nullindia ap khammam 6india up null null |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-04-13 : 14:45:49
|
What you need is a recursive CTE. seehttp://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
MuralikrishnaVeera
Posting Yak Master
129 Posts |
Posted - 2014-04-14 : 06:14:23
|
Can anyone suggest me the best way ..I am sure it's not a perfect way to use Recursive CTECREATE 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 KrishnaYou live only once ..If you do it right once is enough....... |
|
|
MuralikrishnaVeera
Posting Yak Master
129 Posts |
Posted - 2014-04-16 : 05:18:43
|
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 KrishnaYou live only once ..If you do it right once is enough....... |
|
|
|
|
|