CREATE TABLE COMMCODE2
(
SABUN VARCHAR(50) NOT NULL,
CODES VARCHAR(5) NOT NULL,
DATES VARCHAR(20) NOT NULL,
SABUN2 VARCHAR(100),
PRIMARY KEY (SABUN, CHECKCODE, CHECKDATE )
)
위와 같은 테이블이 있습니다.
1111 A01 20170613100508 1010
1111 A02 20170613111116 1010
2222 A01 20170613111148 1010
2222 A02 20170613111148 1010
2222 A03 20170613111148 1010
3333 A01 20170613100508 1010
위의 데이터를 횡으로 보여주고자 합니다.
사번 CODE1, DATA1 CODE2, DATA2, CODE3, DATA3
1111 A01 20170613100508 A02 20170613111116
2222 A01 20170613111148 A02 20170613111148 A03 20170613111148
3333 A01 20170613100508
위와 같이 조회 되는 QUERY를 부탁 드리겠습니다.
(
Select '1111','A01','20170613100508','1010' Union All
Select '1111','A02','20170613111116','1010' Union All
Select '2222','A01','20170613111148','1010' Union All
Select '2222','A02','20170613111148','1010' Union All
Select '2222','A03','20170613111148','1010' Union All
Select '3333','A01','20170613100508','1010'
)
,tblB(SABUN,CODES,DATES,SABUN2,SEQ) As
(
Select a.*
,Row_Number() Over(Partition By a.SABUN Order By a.SABUN Asc,a.DATES ASC)
From tblA a
)
Select a.SABUN
,a.CODES As CODE1
,a.DATES AS DATA1
,b.CODES As CODE2
,b.DATES AS DATA2
,c.CODES As CODE3
,c.DATES AS DATA3
From tblB a
Left Outer Join
tblB b
On a.SABUN = b.SABUN
And b.SEQ = a.SEQ + 1
Left Outer Join
tblB c
On b.SABUN = c.SABUN
And c.SEQ = b.SEQ + 1
Where a.SEQ = 1
Order By a.SABUN Asc