unpivot 쓰고 카운트 하면 될것 같은데 ....

SQL Developer 툴로 사용하는데요 ... 예제로 좀 해주세요 ;;; 

ㅜㅡ

컬럼 개수가 고정이시라면

하기처럼 무식하게 하셔도 될 듯 합니다.

;with tblA(ID,q1,q2,q3,q4,q5,q6) As
(
      Select 'kim'
             ,1
             ,3
             ,5
             ,2
             ,3
             ,3
)
,tblB(ID,data,col_nm) As
(
      Select a.ID
            ,a.q1
            ,'q1'
        From tblA a

   Union All       
           
      Select a.ID
            ,a.q2
            ,'q2'
        From tblA a

   Union All
  
      Select a.ID
            ,a.q3
            ,'q3'
        From tblA a

   Union All
  
      Select a.ID
            ,a.q4
            ,'q4'
        From tblA a

   Union All
  
      Select a.ID
            ,a.q5
            ,'q5'
        From tblA a

   Union All
  
      Select a.ID
            ,a.q6
            ,'q6'
        From tblA a
)     
      Select Count(1)
        From tblB a
       Where a.ID = 'kim'
         And a.data = 1
        

declare @t table (id varchar(max), q1 int, q2 int, q3 int, q4 int, q5 int, q6 int)

insert into @t values ('kim', 1, 3, 4, 2, 3, 3)

declare @id varchar(max) = 'kim'

,              @value int = 1

--,            @value int = 3

select  sum_value = sum(q1 + q2 + q3 + q4 + q5 + q6)

from    (

                       select  q1 = (case when q1 = @value then 1 else 0 end)

                       ,              q2 = (case when q2 = @value then 1 else 0 end)

                       ,              q3 = (case when q3 = @value then 1 else 0 end)

                       ,              q4 = (case when q4 = @value then 1 else 0 end)

                       ,              q5 = (case when q5 = @value then 1 else 0 end)

                       ,              q6 = (case when q6 = @value then 1 else 0 end)

                       from    @t

                       where   id = @id

               ) as a