create table #temp_lib1
(
a char(1),
b varchar(10),
c int
)
insert into #temp_lib1 values('A', '상품1', 10)
insert into #temp_lib1 values('A', '상품2', 20)
insert into #temp_lib1 values('A', '상품3', 30)
insert into #temp_lib1 values('B', '상품1', 10)
insert into #temp_lib1 values('C', '상품3', 15)
insert into #temp_lib1 values('A', '상품3', 40)
insert into #temp_lib1 values('B', '상품4', 50)
insert into #temp_lib1 values('C', '상품2', 10)
insert into #temp_lib1 values('C', '상품3', 20)
insert into #temp_lib1 values('D', '상품2', 20)
insert into #temp_lib1 values('C', '상품5', 20)
select a 창고, b 상품, c 수량 from #temp_lib1
-----------------------------
출력
-----------------------------
창고 상품 수량
-----------------------------
A 상품1 10
A 상품2 20
A 상품3 30
B 상품1 10
C 상품3 15
A 상품3 40
B 상품4 50
C 상품2 10
C 상품3 20
D 상품2 20
C 상품5 20
-----------------------------
이렇게 입력되었습니다.
결과를 만드는 쿼리는..
declare @b varchar(10)
declare @sql varchar(8000)
set @sql = 'select x.a 창고, '
declare c_cursor cursor for
select b
from #temp_lib1
group by b
order by b for read only
open c_cursor
fetch next from c_cursor into @b
while @@fetch_status = 0
begin
set @sql = @sql + 'max((case x.b when ''' + cast(@b as varchar(10)) + ''' then x.sum_c else 0 end)) ' + cast(@b as varchar(10)) +', '
fetch next from c_cursor into @b
end
close c_cursor
deallocate c_cursor
set @sql = left(@sql, len(@sql)-1)
set @sql = @sql + ' from '
set @sql = @sql + '( select a, b, sum(c) sum_c '
set @sql = @sql + ' from #temp_lib1 '
set @sql = @sql + ' group by a, b) as x '
set @sql = @sql + 'group by x.a '
set @sql = @sql + 'order by x.a'
exec(@sql)
-----------------------------------------------------------
출력
-----------------------------------------------------------
창고 상품1 상품2 상품3 상품4 상품5
-----------------------------------------------------------
A 10 20 70 0 0
B 10 0 0 50 0
C 0 10 35 0 20
D 0 20 0 0 0
-----------------------------------------------------------