유형별 카운트

-- 테이블생성
CREATE TABLE [TimeBook] (
     [No] [int] IDENTITY (1, 1) NOT NULL ,
     [Name] [varchar] (10) COLLATE Korean_Wansung_CI_AS NULL ,
     [PersonalID] [char] (10) COLLATE Korean_Wansung_CI_AS NOT NULL ,
     [Date] [varchar] (10) COLLATE Korean_Wansung_CI_AS NULL ,
     [TimeBookST] [varchar] (5) COLLATE Korean_Wansung_CI_AS NULL ,
     [TimeBookED] [varchar] (5) COLLATE Korean_Wansung_CI_AS NULL ,
     [TimeBookCate] [char] (1) COLLATE Korean_Wansung_CI_AS NULL ,
  CONSTRAINT [PK_TimeBook] PRIMARY KEY CLUSTERED 
  (
       [No]
  ) ON [PRIMARY] 
) ON [PRIMARY]
GO

-- 집계 쿼리
SELECT A.Name
     , A.PersonalID
     , max(Total1) as '일근'
     , max(Total2) as '철야'
     , max(Total3) as '비번'
FROM (SELECT Name
           , PersonalID
           , (case TimeBookCate when '1' then count(*) end) Total1
           , (case TimeBookCate when '2' then count(*) end) Total2
           , (case TimeBookCate when '3' then count(*) end) Total3
        FROM Timebook
        GROUP BY Name, PersonalID, TimeBookCate
     ) A
GROUP BY A.Name, A.PersonalID