Case

-- 테이블 생성.

/*

결과가 아래와 같게.

code | good1 | good2 | good3 

 001  | 2개      | 1개    | 1개 

 002  | 1개      | 0개    | 1개 

*/

create table good_out 

(

code varchar(100) null,

goodname varchar(100) null

)

go  

-- 데이타 입력.

insert into good_out

select '001','good1' union all

select '001','good2' union all

select '001','good1' union all

select '001','good3' union all

select '002','good1' union all

select '002','good3'

-- 테이블 및 데이타 확인.

select * from good_out 

 

  -- - 정답 -

  declare @EA varchar(2);

  set @EA = '개'

SELECT code

, count(case when goodname = 'good1' then goodname + @EA else null end) as 'good1'

, count(case when goodname = 'good2' then goodname + @EA else null end) as 'good2'

, count(case when goodname = 'good3' then goodname + @EA else null end) as 'good3'

FROM good_out

group by code