A테이블
===================================
company loginDT loginURL
-----------------------------------------------------------
A사 2016-01-01 www.a.co.kr
A사 2016-01-01 www.b.co.kr
A사 2016-01-01 www.c.co.kr
=====================================
이상과 같은 테이블 정보가 있을때
월별 사이트별 통계를 아래와 같이 결과를 추출하려면... 쿼리작성을 어떻게 해야 할까요?
======================================================================================
company login_Month www.a.co.kr www.b.co.kr www.c.co.kr TotalCnt
-------------------------------------------------------------------------------------------------------------------------------------------------
A사 2016-01 1 1 1 3
======================================================================================
CREATE TABLE #A(
COMPANY VARCHAR(20),
LOGINDT VARCHAR(20),
LOGINURL VARCHAR(100)
)
INSERT INTO #A VALUES('A사','2016-01-01','www.a.co.kr')
go
INSERT INTO #A VALUES('A사','2016-01-01','www.b.co.kr')
go
INSERT INTO #A VALUES('A사','2016-01-01','www.c.co.kr')
INSERT INTO #A VALUES('A사','2016-02-01','www.a.co.kr')
go
INSERT INTO #A VALUES('A사','2016-02-01','www.b.co.kr')
go
INSERT INTO #A VALUES('A사','2016-02-01','www.c.co.kr')
go
INSERT INTO #A VALUES('A사','2016-02-04','www.c.co.kr')
go
INSERT INTO #A VALUES('A사','2016-02-06','www.c.co.kr')
go
INSERT INTO #A VALUES('A사','2016-02-07','www.c.co.kr')
go
select * from #A
select *,[www.a.co.kr]+ [www.b.co.kr]+[www.c.co.kr] as TotalCnt
from
(
select COMPANY,substring(logindt,0,8) as login_month, loginurl
from #A
) src
pivot
(
count(loginurl)
for loginurl in ([www.a.co.kr], [www.b.co.kr], [www.c.co.kr])
) piv