CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Nikunj Soni
Nikunj Soni

Reputation: 297

SQL Server 2012 database query issue

I have a query which I am trying to resolve but could not able to make it happen.

Below is the query which needs to evaluate.

Declare @Table Table(SSYId INT, Name VARCHAR(100), Address VARCHAR(100), ParentSSYId INT, RelationWithParentSSY VARCHAR(50))
INSERT INTO @Table VALUES (1,'A','Maninagar, Ahmedabad',2,'Wife')
INSERT INTO @Table VALUES (2,'B','Maninagar, Ahmedabad',NULL,NULL)
INSERT INTO @Table VALUES (3,'C','Isanpur, Ahmedabad',NULL,NULL)
INSERT INTO @Table VALUES (4,'D','Isanpur, Ahmedabad',3,'Husband')
INSERT INTO @Table VALUES (5,'E','Gokuldham, Ahmedabad',NULL,NULL)

So the result would be

SSYId   | Name   | Address                  | ParentSSYId   | RelationWithParentSSY 
 1      | 'A'    | 'Maninagar, Ahmedabad'   | 2             | 'Wife'
 2      | 'B'    | 'Maninagar, Ahmedabad'   | NULL          | NULL
 3      | 'C'    | 'Isanpur, Ahmedabad'     | NULL          | NULL
 4      | 'D'    | 'Isanpur, Ahmedabad'     | 3             | 'Husband'
 5      | 'E'    | 'Gokuldham, Ahmedabad'   | NULL          | NULL

Here I have shown raw data where relation and address is varchar field in my db I have created foreign key. Expected outcome is mentioned below.

PrimaryName    | SecondaryName    | Address
A              | B                | 'Maninagar, Ahmedabad'
C              | D                | 'Isanpur, Ahmedabad'
E              | NULL             | 'Gokuldham, Ahmedabad'

In the result you can see Husband's name should come in the PrimaryName and Wife's name should come in the SecondaryName. If no relation specified with any other then only it shows in PrimaryName and SecondaryName should be blank or null.

My try to get expected result.

SELECT DISTINCT STUFF((SELECT ',' + T2.Name FROM @Table T2 WHERE T2.ParentSSYId = T.SSYId ORDER BY T2.SSYId FOR XML PATH('')),1,1,'') AS PrimaryName,
T1.Name AS SecondaryName,
T1.Address AS Address
FROM @Table T
INNER JOIN @Table T1
ON T.SSYId = T1.ParentSSYId   
GROUP BY T.SSYId,T.Name,T.ParentSSYId,T.Address

Here in above query I don't know how to check whether it is husband or wife so I have to put it in the first column.

Your help would be much appreciated.

Thank you in advance.

Nikunj

Upvotes: 1

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you basically just need a case statement:

select (case when tparent.SSYId is null or tparent.RelationWithParentSSY = 'wife'
             then t.Name
             else tparent.Name
        end) as PrimaryName,
       (case when tparent.SSYId is null or tparent.RelationWithParentSSY = 'wife'
             then tparent.Name
             else t.Name
        end) as SecondaryName
       t.address
from @Table t left join
     @Table tparent
     on t.SSYId = tparent.ParentSSYId
where t.ParentSSYId is null;

Actually, you might find the logic in terms of "husband" clearer:

select (case when tparent.RelationWithParentSSY = 'husband'
             then tparent.Name
             else t.Name
        end) as PrimaryName,
       (case when tparent.RelationWithParentSSY = 'husband'
             then t.Name
             else tparent.Name
        end) as SecondaryName
       t.address
from @Table t left join
     @Table tparent
     on t.SSYId = tparent.ParentSSYId
where t.ParentSSYId is null

Upvotes: 2

Related Questions