데이터베이스 질문과 답변
1. 원하는 결과는 첨부한 이미지와 같고
시도해본 코드는 다음과 같습니다.
select *
from (
SELECT [customercode]
,[customer]
,[yr]
,[f1], [f2], [f3]
--,concat(f2,left(concat(rtrim(CONVERT(char, [f3])),'00000'), 5),f4) as s
,[f4]
,iif( [f5] = 0, f6, f5) as result
FROM [ilt].[dbo].[00balance_sheet]
where yr in ( 202112, 202012, 201912) and customercode=1579
order by yr, f2, f4, f3
) as result
pivot (
min(f3)
for yr in ([201912], [202012], [202112])
) as pivot_result
2. 아래는 임시테이블 생성코드입니다. (답변 조금이라도 편하시라고 적었습니다)
with
a (customercode, customer, yr, f1, f2, f3, f4, num)
as (
select '1579', '호','201912',' 자산 ','100' ,'0' ,'0' ,'0'
union all select '1579', '호','201912',' Ⅰ.유동자산 ','110' ,'0' ,'2' ,'508688320'
union all select '1579', '호','201912',' ① 당좌자산 ','111' ,'0' ,'3' ,'469698883'
union all select '1579', '호','201912',' 현금 ','111' ,'10100' ,'4' ,'246000'
union all select '1579', '호','201912',' 보통예금 ','111' ,'10300' ,'4' ,'189509674'
union all select '1579', '호','201912',' 정기예금 ','111' ,'10500' ,'4' ,'138580000'
union all select '1579', '호','201912',' 외상매출금 ','111' ,'10800' ,'4' ,'113191209'
union all select '1579', '호','201912',' 단기대여금 ','111' ,'11400' ,'4' ,'10000000'
union all select '1579', '호','201912',' 선급금 ','111' ,'13100' ,'4' ,'18172000'
union all select '1579', '호','201912',' 선급비용 ','111' ,'13300' ,'4' ,'0'
union all select '1579', '호','201912',' ② 재고자산 ','112' ,'0' ,'3' ,'38989437'
union all select '1579', '호','201912',' 상품 ','112' ,'14600' ,'4' ,'38989437'
union all select '1579', '호','201912',' Ⅱ.비유동자산 ','130' ,'0' ,'2' ,'489137779'
union all select '1579', '호','201912',' ① 투자자산 ','131' ,'0' ,'3' ,'2249091'
union all select '1579', '호','201912',' 장기금융상품 ','131' ,'18900' ,'4' ,'0'
union all select '1579', '호','201912',' 기타투자 ','131' ,'19400' ,'4' ,'2249091'
union all select '1579', '호','201912',' ② 유형자산 ','132' ,'0' ,'3' ,'486888688'
union all select '1579', '호','201912',' 시설장치 ','132' ,'19700' ,'4' ,'66637966'
union all select '1579', '호','201912',' 감가상각누계액 ','132' ,'19800' ,'4' ,'-66449150'
union all select '1579', '호','201912',' 토지 ','132' ,'20100' ,'4' ,'220000000'
union all select '1579', '호','201912',' 건물 ','132' ,'20200' ,'4' ,'275000000'
union all select '1579', '호','201912',' 감가상각누계액 ','132' ,'20300' ,'4' ,'-61875000'
union all select '1579', '호','201912',' 기계장치 ','132' ,'20600' ,'4' ,'6688190'
union all select '1579', '호','201912',' 감가상각누계액 ','132' ,'20700' ,'4' ,'-6168631'
union all select '1579', '호','201912',' 차량운반구 ','132' ,'20800' ,'4' ,'156283727'
union all select '1579', '호','201912',' 감가상각누계액 ','132' ,'20900' ,'4' ,'-105607574'
union all select '1579', '호','201912',' 비품 ','132' ,'21200' ,'4' ,'55927727'
union all select '1579', '호','201912',' 감가상각누계액 ','132' ,'21300' ,'4' ,'-53548567'
union all select '1579', '호','201912',' ③ 무형자산 ','133' ,'0' ,'3' ,'0'
union all select '1579', '호','201912',' ④ 기타비유동자산 ','134' ,'0' ,'3' ,'0'
union all select '1579', '호','201912',' 자산총계 ','139' ,'0' ,'1' ,'997826099'
union all select '1579', '호','201912',' 부채 ','140' ,'0' ,'0' ,'0'
union all select '1579', '호','201912',' Ⅰ.유동부채 ','141' ,'0' ,'2' ,'33730104'
union all select '1579', '호','201912',' 외상매입금 ','141' ,'25100' ,'4' ,'-4995622'
union all select '1579', '호','201912',' 미지급금 ','141' ,'25300' ,'4' ,'-176500'
union all select '1579', '호','201912',' 예수금 ','141' ,'25400' ,'4' ,'-2606720'
union all select '1579', '호','201912',' 가수금 ','141' ,'25700' ,'4' ,'0'
union all select '1579', '호','201912',' 단기차입금 ','141' ,'26000' ,'4' ,'0'
union all select '1579', '호','201912',' 미지급세금 ','141' ,'26100' ,'4' ,'-25951262'
union all select '1579', '호','201912',' Ⅱ.비유동부채 ','142' ,'0' ,'2' ,'308565783'
union all select '1579', '호','201912',' 장기차입금 ','142' ,'29300' ,'4' ,'-25000000'
union all select '1579', '호','201912',' 임직원등장기차입금 ','142' ,'30300' ,'4' ,'-283565783'
union all select '1579', '호','201912',' 부채총계 ','149' ,'0' ,'1' ,'342295887'
union all select '1579', '호','201912',' 자본 ','150' ,'0' ,'0' ,'0'
union all select '1579', '호','201912',' Ⅰ.자본금 ','151' ,'0' ,'2' ,'655530212'
union all select '1579', '호','201912',' 자본금 ','151' ,'33100' ,'4' ,'-12000000'
union all select '1579', '호','201912',' 이월이익잉여금 ','151' ,'37500' ,'4' ,'-643530212'
union all select '1579', '호','201912',' (당기순이익) ','158' ,'0' ,'0' ,'0'
union all select '1579', '호','201912',' 자본총계 ','159' ,'0' ,'1' ,'655530212'
union all select '1579', '호','201912',' 부채와자본총계 ','199' ,'0' ,'1' ,'997826099'
union all select '1579', '호','201912',' 당기: 169,134,685 ','1581' ,'0' ,'0' ,'0'
union all select '1579', '호','201912',' 전기: 171,035,429 ','1582' ,'0' ,'0' ,'0'
union all select '1579', '호','202112',' 자산 ','100' ,'0' ,'0' ,'0'
union all select '1579', '호','202112',' Ⅰ.유동자산 ','110' ,'0' ,'2' ,'6985015298'
union all select '1579', '호','202112',' ① 당좌자산 ','111' ,'0' ,'3' ,'5514247429'
union all select '1579', '호','202112',' 현금 ','111' ,'10100' ,'4' ,'-5000'
union all select '1579', '호','202112',' 보통예금 ','111' ,'10300' ,'4' ,'915233238'
union all select '1579', '호','202112',' 외상매출금 ','111' ,'10800' ,'4' ,'4027075501'
union all select '1579', '호','202112',' 단기대여금 ','111' ,'11400' ,'4' ,'10000000'
union all select '1579', '호','202112',' 선급금 ','111' ,'13100' ,'4' ,'15600000'
union all select '1579', '호','202112',' 선급비용 ','111' ,'13300' ,'4' ,'-3336177'
union all select '1579', '호','202112',' 부가세대급금 ','111' ,'13500' ,'4' ,'549679867'
union all select '1579', '호','202112',' ② 재고자산 ','112' ,'0' ,'3' ,'1470767869'
union all select '1579', '호','202112',' 상품 ','112' ,'14600' ,'4' ,'846400'
union all select '1579', '호','202112',' 제품 ','112' ,'15000' ,'4' ,'16763625'
union all select '1579', '호','202112',' 원재료 ','112' ,'15300' ,'4' ,'1453157844'
union all select '1579', '호','202112',' Ⅱ.비유동자산 ','130' ,'0' ,'2' ,'3722854300'
union all select '1579', '호','202112',' ① 투자자산 ','131' ,'0' ,'3' ,'3487455'
union all select '1579', '호','202112',' 기타투자 ','131' ,'19400' ,'4' ,'3487455'
union all select '1579', '호','202112',' ② 유형자산 ','132' ,'0' ,'3' ,'3718904725'
union all select '1579', '호','202112',' 시설장치 ','132' ,'19700' ,'4' ,'116029966'
union all select '1579', '호','202112',' 감가상각누계액 ','132' ,'19800' ,'4' ,'-66633966'
union all select '1579', '호','202112',' 토지 ','132' ,'20100' ,'4' ,'2385965200'
union all select '1579', '호','202112',' 건물 ','132' ,'20200' ,'4' ,'275000000'
union all select '1579', '호','202112',' 감가상각누계액 ','132' ,'20300' ,'4' ,'-89375000'
union all select '1579', '호','202112',' 기계장치 ','132' ,'20600' ,'4' ,'27558190'
union all select '1579', '호','202112',' 감가상각누계액 ','132' ,'20700' ,'4' ,'-6686190'
union all select '1579', '호','202112',' 차량운반구 ','132' ,'20800' ,'4' ,'201180722'
union all select '1579', '호','202112',' 감가상각누계액 ','132' ,'20900' ,'4' ,'-122256774'
union all select '1579', '호','202112',' 비품 ','132' ,'21200' ,'4' ,'88520182'
union all select '1579', '호','202112',' 감가상각누계액 ','132' ,'21300' ,'4' ,'-58803778'
union all select '1579', '호','202112',' 건설중인자산 ','132' ,'21400' ,'4' ,'968406173'
union all select '1579', '호','202112',' ③ 무형자산 ','133' ,'0' ,'3' ,'462120'
union all select '1579', '호','202112',' 상표권 ','133' ,'22000' ,'4' ,'462120'
union all select '1579', '호','202112',' ④ 기타비유동자산 ','134' ,'0' ,'3' ,'0'
union all select '1579', '호','202112',' 자산총계 ','139' ,'0' ,'1' ,'10707869598'
union all select '1579', '호','202112',' 부채 ','140' ,'0' ,'0' ,'0'
union all select '1579', '호','202112',' Ⅰ.유동부채 ','141' ,'0' ,'2' ,'5596101533'
union all select '1579', '호','202112',' 외상매입금 ','141' ,'25100' ,'4' ,'-4915014160'
union all select '1579', '호','202112',' 미지급금 ','141' ,'25300' ,'4' ,'-53042555'
union all select '1579', '호','202112',' 예수금 ','141' ,'25400' ,'4' ,'16567725'
union all select '1579', '호','202112',' 부가세예수금 ','141' ,'25500' ,'4' ,'-522799577'
union all select '1579', '호','202112',' 가수금 ','141' ,'25700' ,'4' ,'24450000'
union all select '1579', '호','202112',' 미지급세금 ','141' ,'26100' ,'4' ,'-11'
union all select '1579', '호','202112',' 미지급비용 ','141' ,'26200' ,'4' ,'-146262955'
union all select '1579', '호','202112',' Ⅱ.비유동부채 ','142' ,'0' ,'2' ,'2510985231'
union all select '1579', '호','202112',' 장기차입금 ','142' ,'29300' ,'4' ,'-1825000000'
union all select '1579', '호','202112',' 임직원등장기차입금 ','142' ,'30300' ,'4' ,'-685985231'
union all select '1579', '호','202112',' 부채총계 ','149' ,'0' ,'1' ,'8107086764'
union all select '1579', '호','202112',' 자본 ','150' ,'0' ,'0' ,'0'
union all select '1579', '호','202112',' Ⅰ.자본금 ','151' ,'0' ,'2' ,'2600782834'
union all select '1579', '호','202112',' 자본금 ','151' ,'33100' ,'4' ,'-1524224076'
union all select '1579', '호','202112',' 이월이익잉여금 ','151' ,'37500' ,'4' ,'-1076558758'
union all select '1579', '호','202112',' (당기순이익) ','158' ,'0' ,'0' ,'0'
union all select '1579', '호','202112',' 자본총계 ','159' ,'0' ,'1' ,'2600782834'
union all select '1579', '호','202112',' 부채와자본총계 ','199' ,'0' ,'1' ,'10707869598'
union all select '1579', '호','202112',' 당기: 4,156,418,375 ','1581' ,'0' ,'0' ,'0'
union all select '1579', '호','202112',' 전기: 433,028,546 ','1582' ,'0' ,'0' ,'0'
)
select * from a
1. Select 절에는 필요한 항목만 적으시고, 불필요한 항목 삭제.
2. Order By 절도 삭제 하시면 잘 돌아갈 듯 하네요.
3. 컬럼명 정확한지 확인 필요하구요. MIN(f3) 맞나요?
select *
from (
SELECT [customercode]
,[customer]
,[yr]
,[f1]
,[f2]
,[f3]
,[f4]
,iif( [f5] = 0, f6, f5) as result
FROM [ilt].[dbo].[00balance_sheet]
where yr in ( 202112, 202012, 201912) and customercode=1579
) as tb
pivot (
min(result)
for yr in ([201912], [202012], [202112])
) as pivot_result
order by f2, f4, f3
감사합니다 덕분에 완성했습니다~!
) 버튼을 클릭하여 작성 하시면 됩니다.