NESTED CASE WHEN THEN

Question

Hi all,

How can I achieve this? I managed to do this with nested 'case when then' but the query looks complicated and laborious. Is there an easy way to do this?

Answer 1

Hi @Kunder, Santhosha SBOBNG-PTIV/ES

I did the same but the query looks too wordy since the number of columns is more than 40.

Here is a solution using Cursor to generate the query code. Considering you only have about 40 columns, I guess it is acceptable to use Cursor.
Check this:

CREATE TABLE Table1(Userid INT,Fname VARCHAR(20),Mname VARCHAR(20),Lname VARCHAR(20))  
INSERT INTO Table1 VALUES  
(1,'A','B','C'),(2,'X','Y','Z'),(3,'JJ','L','M'),(4,'R','S','T')  
  
CREATE TABLE Table2(Userid INT,Fname VARCHAR(20),Mname VARCHAR(20),Lname VARCHAR(20))  
INSERT INTO Table2 VALUES  
(1,'A','EE','C'),(2,'X','Y','UU'),(3,'K','PP','M'),(4,'R','S','')  
  
DECLARE @SQL_String NVARCHAR(MAX)='SELECT A.Userid'  
  
DECLARE Cursor_ColumnName CURSOR LOCAL   
FOR   
SELECT COLUMN_NAME  
FROM INFORMATION_SCHEMA.COLUMNS   
WHERE TABLE_NAME = 'Table1' AND COLUMN_NAME<>'Userid'   
  
OPEN Cursor_ColumnName   
DECLARE @ColumnName NVARCHAR(30)  
  
FETCH NEXT FROM Cursor_ColumnName INTO @ColumnName  
WHILE (@@FETCH_STATUS = 0)  
BEGIN  
 SET @SQL_String=@SQL_String+',CASE WHEN A.'+@ColumnName+'=B.'+@ColumnName+' THEN '''' ELSE A.'+@ColumnName+' END AS '+@ColumnName+'_Table1  
                              ,CASE WHEN A.'+@ColumnName+'=B.'+@ColumnName+' THEN '''' ELSE B.'+@ColumnName+' END AS '+@ColumnName+'_Table2'  
 FETCH NEXT FROM Cursor_ColumnName INTO @ColumnName  
END  
  
CLOSE Cursor_ColumnName   
DEALLOCATE Cursor_ColumnName   
  
SET @SQL_String = @SQL_String+' FROM Table1 A JOIN Table2 B ON A.Userid=B.Userid'  
--PRINT @SQL_String  
EXEC(@SQL_String)  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

A simple JOIN between two tables should work, either inner join or left join.

Answer 3

I would just join 2 tables on ID and use case when T1.FName = T2.FName then '' else T1.FName end as T1_FName, etc.

I don't think it's worth trying to simplify this query.

Answer 4

If the query looks complicated and laborous, that is not very surprising. The operation is non-relational, that is, it goes against the basic principles of a relational databases. There can certainly be good reasons run such queries from time to time, but nevertheless breaking the rules typically comes with a price.

As Viorel suggests, it may be better to generate such a beast dynamically.