[SQL] 각 행에 null이 아닌 컬럼의 카운트

각 행에 null이 아닌 컬럼의 카운트

나는 4 열을 포함하는 테이블이 있고 5 번째 열에서 나는 예를 들어 이전 4의 거기 얼마나 많은 null이 아닌 컬럼의 수를 저장할 :

여기서 x는 값은 다음과 같습니다

Column1 | Column2 | Column3 | Column4 | Count
  X     |    X    |   NULL  |    X    |   3
 NULL   |   NULL  |    X    |    X    |   2
 NULL   |   NULL  |   NULL  |   NULL  |   0

해결법

1. ==============================

   1.

   select
       T.Column1,
       T.Column2,
       T.Column3,
       T.Column4,
       (
           select count(*)
           from (values (T.Column1), (T.Column2), (T.Column3), (T.Column4)) as v(col)
           where v.col is not null
       ) as Column5
   from Table1 as T
   

2. ==============================

   2.

   SELECT   Column1,
            Column2,
            Column3,
            Column4,
            CASE WHEN Column1 IS NOT NULL THEN 1 ELSE 0 END + 
            CASE WHEN Column2 IS NOT NULL THEN 1 ELSE 0 END + 
            CASE WHEN Column3 IS NOT NULL THEN 1 ELSE 0 END + 
            CASE WHEN Column4 IS NOT NULL THEN 1 ELSE 0 END AS Column5
   FROM     Table
   

3. ==============================

   3.

   SELECT Column1, Column2, Column3, Column4,
     Column5 = LEN(COALESCE(LEFT(Column1,1),'')) 
             + LEN(COALESCE(LEFT(Column2,1),''))
             + LEN(COALESCE(LEFT(Column3,1),'')) 
             + LEN(COALESCE(LEFT(Column4,1),''))
    FROM dbo.YourTable;
   

   데모:

   DECLARE @x TABLE(a VARCHAR(32),b INT,c VARCHAR(32),d VARCHAR(32));
   
   INSERT @x VALUES
   ('01',3023,NULL,'blat'),
   ('02',NULL, NULL,'blat'),
   ('03',5,NULL,'blat'),
   ('04',24,'bo','blat'),
   (NULL, NULL, NULL, NULL);
   
   SELECT a, b, c, d,
       LEN(COALESCE(LEFT(a,1),'')) 
     + LEN(COALESCE(LEFT(b,1),''))
     + LEN(COALESCE(LEFT(c,1),'')) 
     + LEN(COALESCE(LEFT(d,1),''))
    FROM @x;
   

from https://stackoverflow.com/questions/18193365/count-of-non-null-columns-in-each-row by cc-by-sa and MIT license