arun arun - 7 months ago 9
SQL Question

How to compare Column data and replace text using SQL Query

Here is my table schema

Table1
{
Column1 nVarchar(MAX),
Column2 nVarchar(MAX),
Column3 nVarchar(MAX),
Column4 nVarchar(MAX),
Column5 nVarchar(MAX),
Column6 nVarchar(MAX),
Column7 nVarchar(MAX),
Column8 nVarchar(MAX),
Column9 nVarchar(MAX),

}


How shall i compare Column1 with Column2 , Column3 ... Column9 and if the Column2 data is same as Column1 make the Column2 Empty and do the same with other columns

Answer

I couldn't be bothered to type out all 9 columns on IPad but the below will do what you need.

You need to add the other 7 columns into the set, values and pivot following the pattern of the first two.

UPDATE t1
 SET Column1 = [1],
     Column2 = [2]
FROM #table1 t1
CROSS APPLY
(
SELECT *
FROM
(
SELECT  DENSE_Rank() OVER (ORDER BY MIN(Idx)) AS Rnk, 
        ColumnValue
FROM
(
VALUES(1, Column1),(2, Column2)) V(Idx, ColumnValue)
WHERE ColumnValue IS NOT NULL
GROUP BY ColumnValue
) D
PIVOT (MAX(ColumnValue) FOR Rnk IN ([1], [2])) AS P
) ca