안녕하세요.
쿼리 초보자입니다.
혼자 생각해보다 도저히 풀리지 않아 질문드립니다.
userId | datetime |
1 | 2030-01-01 |
2 | 2030-02-01 |
3 | 2030-01-02 |
1 | 2030-01-02 |
1 | 2030-04-01 |
위와 같은 테이블을
userId | 2030-01-01 | 2030-01-02 | 2030-01-03 | (생략된 날짜) | 2030-04-01 |
1 | O | O | X | … | O |
2 | X | X | X | … | X |
3 | X | O | X | … | X |
이러한 형태로 만들고 싶습니다.
제시된 테이블은 유저가 접속할 때마다 해당 유저 아이디와 접속 시간이 쌓이는 테이블입니다.
이 테이블을 가지고 처음 날짜부터 현재까지 유저들이 각 날짜에 접속을 했다면 O, 하지 않았다면 X으로 만들고 싶습니다.
처음에는 단순히 행을 열로 만들어야하다보니, CASE WHEN 을 사용하면 되겠구나 했는데 모든 날짜를 쓰기엔 어려움이 있어서요.
다음으로 고려했던 점은, 재귀 CTE로 만드는 것이었는데 재귀 CTE에는 JOIN이 되지 않고 UNION 만 되더라구요.
(초보자라 잘 몰랐습니다..)
재귀 CTE로 결과 값을 만들고 이를 다시 행을 열로 전환시켜야하는지?.. 갈피가 잘 안 잡힙니다...
어떻게 접근하면 좋을지요?
쿼리를 써주시지 않아도 아이디어나 어떠한 개념을 이용하면 되는지만 말씀해주셔도 정말 감사합니다.
좋은 하루 되세요.
저도 초보라서 구현 해보고 싶어서 인터넷 검색 해서 이것 저것 한번 해봤는데 이렇게 하면 어떨까요?
declare @work_dt datetime = '2030-01-01', @add_dt datetime
create table #t
( id int, dt datetime
)
insert into #t
SELECT 1 id, '2030-01-01' dt
UNION ALL SELECT 2, '2030-01-10'
UNION ALL SELECT 3, '2030-01-02'
UNION ALL SELECT 1, '2030-01-02'
UNION ALL SELECT 1, '2030-01-20'
create table #final
(id int ,dt datetime, yn char(1) )
set @add_dt = @work_dt
while @add_dt <= '2030-01-31'
begin
insert into #final
select id, dt, case when @add_dt = dt then 1 else 0 end from #t where dt = @add_dt
set @add_dt = DATEADD(day, +1, @add_dt)
end
declare @colums nvarchar(max)
declare @sql nvarchar(max)
set @colums = ''
select @colums = @colums + '[' + yyyymmdd + '],'
from (
select distinct
convert(nvarchar(10), dt, 21) as yyyymmdd
from #final where dt between @work_dt and '2030-01-31'
) as dt
set @colums = left(@colums, len(@colums) -1)
set @sql = '
select *
from (
select * from #final) as r
pivot ( count(yn) for dt in (' + @colums + '))
as rs
'
exec (@sql)
drop table #t
drop table #final