Author |
Topic |
ravininave
Posting Yak Master
111 Posts |
Posted - 2012-03-20 : 03:28:55
|
My Table:Table Name AppMastName,AppID,SpID,LeftRighta,2,1,0b,3,1,1c,4,2,0d,5,3,0e,6,2,1f,7,4,00=Left, 1=Right PositionNow I have to show the data of all members with their Left Right Members likea - c(left) - e(right)b - d - c - fVB6/ASP.NET------------------------http://www.nehasoftec.com |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-03-20 : 12:02:26
|
[code]DECLARE @MyTable TABLE (Name CHAR(1), AppID INT, SpID INT, LeftRight INT)INSERT @MyTableVALUES('a',2,1,0),('b',3,1,1),('c',4,2,0),('d',5,3,0),('e',6,2,1),('f',7,4,0)SELECT Start.Name, LeftTable.Name AS [Left], RightTable.Name AS [Right]FROM @MyTable AS StartLEFT OUTER JOIN @MyTable AS LeftTable ON Start.AppID = LeftTable.SpID AND LeftTable.LeftRight = 0LEFT OUTER JOIN @MyTable AS RightTable ON Start.AppID = RightTable.SpID AND RightTable.LeftRight = 1WHERE LeftTable.Name IS NOT NULL OR RightTable.Name IS NOT NULL[/code] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-20 : 15:33:03
|
[code]SELECT t.Name,Left,RightFROM table tOUTER APPLY (SELECT MAX(CASE WHEN LeftRight=0 THEN Name END) AS Left,MAX(CASE WHEN LeftRight=1 THEN Name END) AS RightFROM tableWHERE SpID = t.AppID)t1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|