Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to do this

Author  Topic 

ravininave
Posting Yak Master

111 Posts

Posted - 2012-03-20 : 03:28:55
My Table:
Table Name AppMast
Name,AppID,SpID,LeftRight
a,2,1,0
b,3,1,1
c,4,2,0
d,5,3,0
e,6,2,1
f,7,4,0

0=Left, 1=Right Position
Now I have to show the data of all members with their Left Right Members like

a - c(left) - e(right)
b - d -
c - f





VB6/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 @MyTable
VALUES
('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 Start
LEFT OUTER JOIN
@MyTable AS LeftTable
ON Start.AppID = LeftTable.SpID
AND LeftTable.LeftRight = 0
LEFT OUTER JOIN
@MyTable AS RightTable
ON Start.AppID = RightTable.SpID
AND RightTable.LeftRight = 1
WHERE
LeftTable.Name IS NOT NULL
OR RightTable.Name IS NOT NULL[/code]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 15:33:03
[code]
SELECT t.Name,Left,Right
FROM table t
OUTER APPLY (SELECT MAX(CASE WHEN LeftRight=0 THEN Name END) AS Left,
MAX(CASE WHEN LeftRight=1 THEN Name END) AS Right
FROM table
WHERE SpID = t.AppID
)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -