SQL: Linking Column Value to Column Name to fulfill some conditions-CodePudding

Base on the image, the TABLE B is my main table while the TABLE A is the rules or conditions for the TABLE B.

I want to write a query such that when rules 1 in Table A, which stands for column [C1] having indicator 3. Then find the respective column name in TABLE B with respective value and replace the FILL value. The result that I wanted is like TABLE C.

How can I do so?

CodePudding user response：

You can achieve this using pivot. Changing column names in table A from rows into columns.

select B.c1,B.c2, (CASE WHEN A.ind IS NULL THEN 0 ELSE 1 END) AS fill
from tblB AS B
left outer join
(select *
from 
(
  select [column], ind, fill
  from tblA 
) src
pivot
(
  max(fill)
  for [column] in ([c1], [c2])
) piv
) AS A
ON (B.c1 = A.ind or B.c2 = A.ind)

CodePudding user response：

You just need to join based on an OR condition over both columns

Preferably aggregate with MAX just in case there are multiple matches

SELECT
  b.c1,
  b.c2,
  ISNULL(a.fill, b.fill) AS fill
FROM TableB b
OUTER APPLY (
    SELECT MAX(fill) AS fill
    FROM TableA a
    WHERE a.[column] = 'c1' AND b.c1 = a.ind
       OR a.[column] = 'c2' AND b.c2 = a.ind
) a;