Myujth

I have a T-SQL select statement with multiple columns with Case statements yes or no based on another table data

I have these two tables:

table1

Id | Location
----+------------
101 | Warsaw
102 | Belfast
104 | Singapore
106 | Texas
108 | Mumbai
109 | Virgina

table2:

Id | AttrName | AttrId
----+------------+---------
101 | Datacenter | 99
101 | Cold | 88
102 | Datacenter | 99
102 | Cold | 88
106 | Datacenter | 99
108 | Cold | 88

The query output should be:

Id | Location | Datacenter | Cold 
----+-----------+------------+------
101 | Warsaw | YES | YES 
102 | Belfast | YES | YES 
104 | Singapore | NO | NO 
106 | Texas | YES | NO 
108 | Mumbai | NO | YES 
109 | Virgina | NO | NO 

Please let me know how we can achieve this.

I'd use PIVOT instead. It can perform better, but I'd check the execution plans on each to be sure. Just be sure to redirect my query to the real tables (instead of these table variables for testing) before you compare plans. Good luck! :)

DECLARE @Table1 TABLE (
 Id BIGINT NOT NULL PRIMARY KEY,
 Location VARCHAR(100) NOT NULL,
 UNIQUE CLUSTERED (Id)
);

INSERT INTO @Table1
(Id, Location)
VALUES
(101,'Warsaw'),
(102,'Belfast'),
(104,'Singapore'),
(106,'Texas'),
(108,'Mumbai'),
(109,'Virgina');

DECLARE @Table2 TABLE (
 Id BIGINT NOT NULL,
 AttrName VARCHAR(100) NOT NULL,
 AttrId BIGINT NOT NULL,
 UNIQUE CLUSTERED (Id,AttrId)
);

INSERT INTO @Table2
(Id, AttrName, AttrId)
VALUES
(101,'Datacenter',99),
(101,'Cold',88),
(102,'Datacenter',99),
(102,'Cold',88),
(106,'Datacenter',99),
(108,'Cold',88);

WITH CTE_Table2 AS (
 SELECT
 Id,
 Datacenter,
 Cold
 FROM (
 SELECT
 t.Id,
 t.AttrName,
 'YES' AS Bool
 FROM @Table2 AS t
 ) AS b
 PIVOT (
 MAX(Bool)
 FOR AttrName IN ([Datacenter],[Cold])
 ) AS p
)

SELECT
 t1.Id,
 t1.Location,
 ISNULL(t2.Datacenter,'NO') AS Datacenter,
 ISNULL(t2.Cold,'NO') AS Cold
FROM @Table1 AS t1
LEFT JOIN CTE_Table2 AS t2
 ON t1.Id = t2.Id;

Below is other option to resolve, it uses EXISTS,

-- delcare table variables
declare @t1 table (Id int, Location nvarchar(20))
declare @t2 table (Id int, AttrName nvarchar(20), AttrId int)

-- insert test data
insert into @t1
values (101, 'Warsaw'),
 (102, 'Beflast'),
 (104, 'Singapore'),
 (106, 'Texas'),
 (108, 'Mumbai'),
 (109, 'Virginia')

insert into @t2
values (101, 'Datacentre', 99),
 (101, 'Cold', 88),
 (102, 'Datacentre', 99),
 (102, 'Cold', 88),
 (106, 'Datacentre', 99),
 (108, 'Cold', 88)

-- query
select 
 t1.Id,
 Location, 
 case when exists (select 1 from @t2 t2 where t2.AttrId = 99 and t1.id = t2.id) then 'yes' else 'no' end as Datacentre,
 case when exists (select 1 from @t2 t2 where t2.AttrId = 88 and t1.id = t2.id) then 'yes' else 'no' end as Cold
from @t1 t1

Result

Id Location Datacentre Cold
101 Warsaw yes yes
102 Beflast yes yes
104 Singapore no no
106 Texas yes no
108 Mumbai no yes
109 Virginia no no

1. Simple but not optimized approach: You need to LEFT OUTER JOIN table2 to table1 as many times as many AttrName's you would like to put in different columns in the final result. Each join should use a different alias name for table2 and filter on a specific AttrName:

FROM table1 as base
LEFT OUTER JOIN table2 as Datacenter on base.Id = Datacenter.Id and Datacenter.AttrName = 'Datacenter'
LEFT OUTER JOIN table2 as Cold on base.Id = Cold.Id and Cold.AttrName = 'Cold'

In the SELECT you need to check for the existence of matching lookup to the specific alias of table2 to define a column deriving from AttrName:

CASE WHEN Datacenter.Id IS NOT NULL THEN 'YES' ELSE 'NO' END as Datacenter

2. Advanced and likely more optimized approach:
   
   Use Utrolig's answer: PIVOT should outperform due to less lookups (but always look at query plans when you optimize code!)