login register Sysop! about ME  

2018년 12월 20일 11시 32분 00초,     조회수 : 322
  제목 : RE : 쿼리문 문의 드립니다.
SEQ : 28405 글쓴이 : supercore

작성 포맷 : TEXT 모드, 자동 줄바꿈 사용


declare @temp table (
	   items	   nvarchar(20)
	   ,sales_month	   nvarchar(2)
	   ,sales_box	   	   int
)

declare @unit table (
	   items	   nvarchar(20)
	   ,unit	   nvarchar(10)
)

insert into @temp values ('사과', '01', 6)
insert into @temp values ('사과', '01', 3)
insert into @temp values ('딸기', '01', 2)
insert into @temp values ('포도', '03', 4)
insert into @temp values ('포도', '06', 5)
insert into @temp values ('사과', '07', 4)

insert into @unit values ('사과','box')
insert into @unit values ('포도','box')
insert into @unit values ('딸기','box')
insert into @unit values ('배','box')


select * from @temp;

with cte_pivot as (
	   select
	   	   items
	   	   ,isnull([01], 0) as m_1
	   	   ,isnull([02], 0) as m_2
	   	   ,isnull([03], 0) as m_3
	   	   ,isnull([04], 0) as m_4
	   	   ,isnull([05], 0) as m_5
	   	   ,isnull([06], 0) as m_6
	   	   ,isnull([07], 0) as m_7
	   	   ,isnull([08], 0) as m_8
	   	   ,isnull([09], 0) as m_9
	   	   ,isnull([10], 0) as m_10
	   	   ,isnull([11], 0) as m_11
	   	   ,isnull([12], 0) as m_12
	   from (
	   	   select
	   	   	   items, sales_month, sales_box
	   	   from @temp
	   ) as t
	   pivot (
	   	   sum(sales_box) for sales_month in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])
	   ) as pv
)

select
	   a.items
	   ,isnull(m_1, 0) as m_1
	   ,isnull(m_2, 0) as m_2
	   ,isnull(m_3, 0) as m_3
	   ,isnull(m_4, 0) as m_4
	   ,isnull(m_5, 0) as m_5
	   ,isnull(m_6, 0) as m_6
	   ,isnull(m_7, 0) as m_7
	   ,isnull(m_8, 0) as m_8
	   ,isnull(m_9, 0) as m_9
	   ,isnull(m_10, 0) as m_10
	   ,isnull(m_11, 0) as m_11
	   ,isnull(m_12, 0) as m_12
	   ,isnull(total_box, 0) as sales_total
from @unit as a
	   left outer join (
	   	   select 
	   	   	   items
	   	   	   ,m_1
	   	   	   ,m_2
	   	   	   ,m_3
	   	   	   ,m_4
	   	   	   ,m_5
	   	   	   ,m_6
	   	   	   ,m_7
	   	   	   ,m_8
	   	   	   ,m_9
	   	   	   ,m_10
	   	   	   ,m_11
	   	   	   ,m_12	   
	   	   	   ,(m_1 + m_2 + m_3 + m_4 + m_5 + m_6 + m_7 + m_8 + m_9 + m_10 + m_11 + m_12) as 
total_box
	   	   from cte_pivot
	   ) as b on a.items = b.items



제목 작성자 날짜 조회
쿼리문 문의 드립니다. rkrkapf153 2018-12-17 341
RE : 쿼리문 문의 드립니다. supercore 2018-12-20 322
상당히 복잡하네요 virgo75 2018-12-19 335

 
 
.NET과 Java 동영상 기반의 교육사이트

로딩 중입니다...

서버 프레임워크 지원 : NeoDEEX
based on ASP.NET 3.5
Creative Commons License
{5}
{2} 읽음   :{3} ({4})