SQL서버 쿼리문의 pivot 질문드립니다.

Just 2023.03.21 10:06 Views : 228

안녕하세요.

pivot 관련해서 쿼리 작성중인데 어떻게 해야 하는지 문의 드립니다..

select *
from (
       select a.setnum
       ,a.ProductNum
           ,a.ProductName
           ,a.ProductCode
           ,a.Yield
           ,b.OutputYear
           ,b.Outputmonth
           ,b.OutputQty
           ,SUM(b.OutputQty) OVER(partition by b.outputProductNum) AS SUM_Product
       from MPVProduct A, MPVData B
       where a.SetNum = b.OutputSetNum
       and a.ProductNum = b.OutputProductNum
   ) As result
Pivot
(
   sum(OutputQty) for Outputmonth in ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
) as pivot_result
order by OutputYear

주석 2023-03-21 100211.png

연도와 월 데이터는 계속 쌓이는 형태입니다.

위의 결과에서 아래처럼 연도_월 컬럼으로 변경해서 옆으로 쭉 출력이 되게 가능할까요?

알려주시면 감사하겠습니다!!

주석 2023-03-21 100443.png

I want to

cmd_document_vote_user Update Log List

Comment 6

지영아빠

2023.03.21 11:58

MPVPRoduct, MPVData 샘플데이터를 각각 줘보시겠어요?

cmd_comment_vote_user Upvote0 Downvote0 Comment
Just

2023.03.21 14:41

테이블 생성쿼리와 샘플 데이터 입력 쿼리입니다.

CREATE TABLE [dbo].[MPVData](
   [OutputSetNum] [int] NOT NULL,
   [OutputProductNum] [int] NOT NULL,
   [OutputYear] [varchar](4) NOT NULL,
   [OutputMonth] [varchar](10) NOT NULL,
   [OutputQty] [int] NOT NULL,
   [RegUser] [nvarchar](50) NULL,
   [RegDate] [datetime] NOT NULL,
   [UpdDate] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MPVProduct](
   [SetNum] [int] NOT NULL,
   [ProductNum] [int] NOT NULL,
   [ProductName] [nvarchar](100) NULL,
   [ProductCode] [nvarchar](100) NULL,
   [Yield] [int] NULL,
   [RegUser] [nvarchar](50) NULL,
   [RegDate] [datetime] NOT NULL,
   [UpdDate] [datetime] NULL,
CONSTRAINT [PK_OutputQtyProduct] PRIMARY KEY CLUSTERED
(
   [SetNum] ASC,
   [ProductNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

insert into MPVData(OutputSetNum, OutputProductNum, OutputYear, OutputMonth, OutputQty, reguser, regdate)
values
(1,   1   ,'2020',   'May',   23   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   2   ,'2020',   'May',   22   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   3   ,'2020',   'May',   23   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   4   ,'2020',   'May',   23   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   5   ,'2020',   'May',   23   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   6   ,'2020',   'May',   23   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   7   ,'2020',   'May',   8   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   8   ,'2020',   'May',   5   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   9   ,'2020',   'May',   5   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   10   ,'2020',   'May',   5   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   1   ,'2021',   'Jan',   10   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   2   ,'2021',   'Jan',   7   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   3   ,'2021',   'Jan',   135   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   4   ,'2021',   'Jan',   70   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   5   ,'2021',   'Jan',   70   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   6   ,'2021',   'Jan',   71   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   7   ,'2021',   'Jan',   71   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   8   ,'2021',   'Jan',   59   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   9   ,'2021',   'Jan',   59   ,'tester',   '2023-03-16 10:33:28.027')
,(1,   10   ,'2021',   'Jan',   59   ,'tester',   '2023-03-16 10:33:28.027')

insert into MPVProduct(setnum, productnum, productname, productcode, yield, reguser, regdate)
values
(1   ,1   ,'Costner-SPR',   'C11CG05506',   NULL   ,'tester',   '2023-03-16 09:55:41.580')
,(1   ,2   ,'Costner-FPR',   'C11CG02506',   NULL   ,'tester',   '2023-03-16 09:55:49.563')
,(1   ,3   ,'BK XLarge',   'C13T950100',   10000   ,'tester',   '2023-03-16 09:58:02.630')
,(1   ,4   ,'C Large',       'C13T949200',   5000   ,'tester',   '2023-03-16 09:58:02.630')
,(1   ,5   ,'M Large',       'C13T949300',   5000   ,'tester',   '2023-03-16 09:58:02.630')
,(1   ,6   ,'Y Large',       'C13T949400',   5000   ,'tester',   '2023-03-16 09:58:02.630')
,(1   ,7   ,'BK Std',       'C13T948100',   3000   ,'tester',   '2023-03-16 09:58:02.630')
,(1   ,8   ,'C Std',       'C13T948200',   3000   ,'tester',   '2023-03-16 09:58:02.630')
,(1   ,9   ,'M Std',       'C13T948300',   3000   ,'tester',   '2023-03-16 09:58:02.630')
,(1   ,10   ,'Y Std',       'C13T948400',   3000   ,'tester',   '2023-03-16 09:58:02.630')

cmd_comment_vote_user Upvote0 Downvote0 Comment Update Delete
지영아빠

2023.03.21 15:31

칼럼을 동적으로 한다면 해당년의 년월일을 distinct하게 구성한다음 pivot의 동적 칼럼으로구성하시면 될것 같습니다.

select

distinct outputYear + '_' + outputMonth as outputYYMM

from MPVData

select *

from (

select a.setnum

,a.ProductNum

,a.ProductName

,a.ProductCode

,a.Yield

,b.OutputQty

,b.outputYYMM

,SUM(b.OutputQty) OVER(partition by b.outputProductNum) AS SUM_Product

from MPVProduct A,

(select

outputSetNum, OutputProductNum, outputYear + '_' + outputMonth as outputYYMM, outputQty, regUser, regDAte

from MPVData) B

where a.SetNum = b.OutputSetNum

and a.ProductNum = b.OutputProductNum

) As result

Pivot

(

sum(OutputQty) for outputYYMM in (

[2020_May], [2021_Jan] -- 여기 동적 칼럼을 넣으면 될듯요

)

) as pivot_result

cmd_comment_vote_user Upvote0 Downvote0 Comment
Just

2023.03.21 15:52

도움주셔서 감사합니다^^

[2020_May], [2021_Jan] -- 여기 동적 칼럼을 넣으면 될듯요

위 부분에 날짜 데이터가 계속 쌓이게 되면 배열로 만들어서 호출하면 될까요?

cmd_comment_vote_user Upvote0 Downvote0 Comment Update Delete

지영아빠

2023.03.21 16:20

declare @colstring varchar(4000) = ''

select @colstring =  STRING_AGG( '[' + outputYYMM  + ']' ,  ',' ) 
from ( 
    select
    distinct outputYear + '_' + outputMonth as outputYYMM
    from MPVData
) x 

declare @out_head varchar(4000) = '
select *
from (
    select a.setnum
    ,a.ProductNum
    ,a.ProductName
    ,a.ProductCode
    ,a.Yield
    ,b.OutputQty
    ,b.outputYYMM
    ,SUM(b.OutputQty) OVER(partition by b.outputProductNum) AS SUM_Product
    from MPVProduct A,
        (select
        outputSetNum, OutputProductNum, outputYear + ''_'' + outputMonth as outputYYMM, outputQty, regUser, regDAte
        from MPVData) B
        where a.SetNum = b.OutputSetNum
        and a.ProductNum = b.OutputProductNum
    ) As result

    Pivot
    (
    sum(OutputQty) for outputYYMM in ('

declare @out_tail varchar(4000)  = ')
    ) as pivot_result'

exec(@out_head + @colstring + @out_tail)

cmd_comment_vote_user Upvote0 Downvote0 Comment

Just

2023.03.21 16:38

너무너무 감사드립니다^^ 많이 배웠습니다!

cmd_comment_vote_user Upvote0 Downvote0 Comment Update Delete

No.	Subject	Author	Date	Views
Notice	SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트)	코난(김대우)	2023.08.18	36174
Notice	Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트)	코난(김대우)	2021.01.01	18673
10903	종료일자 품목 안보이게 하기 [1]	슈토파이터	2023.03.29	160
10902	안녕하세요 SQL 실행순서에 대해 질문이 있습니다. [3]	이진우5935	2023.03.28	215
»	pivot 질문드립니다. [6]	Just	2023.03.21	228
10900	프로시저안에서 다른 프로시저를 호출하여 다중테이블 결과값 받아서 처리하는 방법이 있나요? [4]	AceCarrot	2023.03.15	198
10899	SQLD, ROLL UP 함수 문제 관련, 답변 주실 수 있는 분 계실까요? [2]	2124	2023.03.06	284
10898	Update는 성공했는데 웹서버가 열리지 않습니다 [5]	잼백	2023.03.01	217
10897	MariaDB로 피벗 구현하는데 쿼리 실행 시간이 너무 오래걸립니다... [2]	ProjectUnknown	2023.02.24	226
10896	win xp에서 win11 22h2(sql2008r2 express) DB서버 접속 실패 문의드립니다.	Cover Fly	2023.02.24	254
10895	문자열 잘라서 join 하여 조회 기능 문의 [1]	문성원	2023.02.18	205
10894	SQL Server 구성관리자에서 네트워크 별칭 입력 시 속성창이 읽기전용이 됩니다. [1]	젝카로델피	2023.02.17	204
10893	사용자별 데이터 조회 [5]	냥냥	2023.02.16	161
10892	저장프로시저 where 조건절 질문 [1]	m****	2023.02.03	165
10891	mssql 조건절 성능에 대한 질문입니다. [2]	rwo	2023.02.03	184
10890	확장저장 프로시저용 dll 파일을 사용중인데 전자서명 에러가 발생하네요 [2]	ksk****	2023.02.01	133
10889	mssql pivot 질문입니다. [1]	rwo	2023.01.27	209
10888	주문번호합계중량에 따른 포장 사이즈 계산 [1]	phh****	2023.01.18	251
10887	SSAS 처리 진행시 SQL 쿼리 DISTINCT 관련 [1]	밤식	2023.01.17	181
10886	MSSQL 입문 강의 사이트나 책 추천해주실 수 있을까요? [2]	그냥토끼	2023.01.10	1006
10885	From 절 테이블 변수 [1]	m****	2022.12.29	338
10884	ms-sql 프로시저에서 입력 파라미터와 해당하는 값 얻기 [1]	m****	2022.12.28	138

Write Tag

First Page 1 2 3 4 5 6 7 8 9 10 Last Page

SQL서버 쿼리문의 pivot 질문드립니다.

Comment 6

지영아빠

Just

지영아빠

Just

지영아빠

Just

SQLER는 소셜로그인을 지원하며, 10초면 가입 후 편안한 댓글 쓰기 & 내가 쓴글 확인이 가능합니다. 소셜로그인으로 이동

커뮤니티

데이터베이스 개발

클라우드 개발

머신러닝/AI개발

파이썬&오픈소스 개발