/**********************************************************************************************
-- Title : [2k] 다양한 Cross-Tab 구현(Pivot, Unpivot)
-- Reference : dBRang.com
-- Key word : cross tab, pivot, unpivot, for xml path 피벗 언피벗
**********************************************************************************************/
/***************************
** Case 1 : 1 X 4 → 4 X 1
***************************/
drop table #ttt
create table #ttt
( a char(1)
, b char(1)
, c char(1)
, d char(1)
)
insert into #ttt values ('q','w','e','r')
-- source set
select * from #ttt
/*
a b c d
---- ---- ---- ----
q w e r
*/
-- middle set
select aa.a, aa.b, aa.c, aa.d, bb.seq
from #ttt aa cross join (select 1 "seq"
union all
select 2 "seq"
union all
select 3 "seq"
union all
select 4 "seq"
) bb
/*
a b c d seq
---- ---- ---- ---- ----
q w e r 1
q w e r 2
q w e r 3
q w e r 4
*/
-- result set
select
case when cc.seq = 1 then cc.a
when cc.seq = 2 then cc.b
when cc.seq = 3 then cc.c
when cc.seq = 4 then cc.d
end as "col"
from
(
select aa.a, aa.b, aa.c, aa.d, bb.seq
from #ttt aa cross join (select 1 "seq"
union all
select 2 "seq"
union all
select 3 "seq"
union all
select 4 "seq"
) bb
) cc
/*
col
----
q
w
e
r
*/
/***************************
** Case 2 : 4 X 1 → 1 X 4
***************************/
drop table #ttt
drop table #ttt2
create table #ttt
( a char(1)
)
insert into #ttt values('q')
insert into #ttt values('w')
insert into #ttt values('e')
insert into #ttt values('r')
-- source set
select * from #ttt
/*
a
----
q
w
e
r
*/
-- middle set(첫행이 unique시 identity 불필요)
select identity(int, 1,1) "id", *
into #ttt2
from #ttt
select * from #ttt2
/*
id a
---- ----
1 q
2 w
3 e
4 r
*/
select id
, case when id = 1 then a else null end "c1"
, case when id = 2 then a else null end "c2"
, case when id = 3 then a else null end "c3"
, case when id = 4 then a else null end "c4"
from #ttt2
/*
id c1 c2 c3 c4
---- ---- ---- ---- ----
1 q NULL NULL NULL
2 NULL w NULL NULL
3 NULL NULL e NULL
4 NULL NULL NULL r
*/
-- result set
select
max(case when id = 1 then a else null end) "c1"
, max(case when id = 2 then a else null end) "c2"
, max(case when id = 3 then a else null end) "c3"
, max(case when id = 4 then a else null end) "c4"
from #ttt2
/*
c1 c2 c3 c4
---- ---- ---- ----
q w e r
*/
/***************************
** Case 3 : 2 X 4 → 4 X 2
***************************/
drop table #ttt
drop table #ttt2
create table #ttt
( a char(1)
, b char(1)
, c char(1)
, d char(1)
)
insert into #ttt values('q','w','e','r')
insert into #ttt values('1','2','3','4')
-- source set
select * from #ttt
/*
a b c d
---- ---- ---- ----
q w e r
1 2 3 4
*/
-- middle set(첫행이 unique시 identity 불필요)
select identity(int,1,1) "seq", *
into #ttt2
from #ttt
select * from #ttt2
/*
seq a b c d
---- ---- ---- ---- ----
1 q w e r
2 1 2 3 4
*/
select seq, no,
case when seq = 1 and no = 1 then a
when seq = 1 and no = 2 then b
when seq = 1 and no = 3 then c
when seq = 1 and no = 4 then d
end "col1"
, case when seq = 2 and no = 1 then a
when seq = 2 and no = 2 then b
when seq = 2 and no = 3 then c
when seq = 2 and no = 4 then d
end "col2"
from #ttt2 aa cross join (select 1 "no"
union all
select 2 "no"
union all
select 3 "no"
union all
select 4 "no"
) bb
/*
seq no col1 col2
---- ---- ---- ----
1 1 q NULL
1 2 w NULL
1 3 e NULL
1 4 r NULL
2 1 NULL 1
2 2 NULL 2
2 3 NULL 3
2 4 NULL 4
*/
-- result set(identity 추가시)
select
max(case when seq = 1 and no = 1 then a
when seq = 1 and no = 2 then b
when seq = 1 and no = 3 then c
when seq = 1 and no = 4 then d
end
) "col1"
, max(case when seq = 2 and no = 1 then a
when seq = 2 and no = 2 then b
when seq = 2 and no = 3 then c
when seq = 2 and no = 4 then d
end
) "col2"
from #ttt2 aa cross join (select 1 "no"
union all
select 2 "no"
union all
select 3 "no"
union all
select 4 "no"
) bb
group by no
/*
col1 col2
---- ----
q 1
w 2
e 3
r 4
*/
-- result set(a 컬럼이 unique한 경우)
select
max(case when a= 'q' and no = 1 then a
when a= 'q' and no = 2 then b
when a= 'q' and no = 3 then c
when a= 'q' and no = 4 then d
end
) "col1"
, max(case when a= '1' and no = 1 then a
when a= '1' and no = 2 then b
when a= '1' and no = 3 then c
when a= '1' and no = 4 then d
end
) "col2"
from #ttt aa cross join (select 1 "no"
union all
select 2 "no"
union all
select 3 "no"
union all
select 4 "no"
) bb
group by no
/***************************
** Case 4 : 4 X 2 → 2 X 4
***************************/
drop table #ttt
drop table #ttt2
create table #ttt
( a char(4)
, b int
)
insert into #ttt values('2000',45)
insert into #ttt values('2001',55)
insert into #ttt values('2002',65)
insert into #ttt values('2005',75)
-- source set
select * from #ttt
/*
a b
---- ----
2000 45
2001 55
2002 65
2005 75
*/
-- middle set(a 컬럼이 unique한 경우 identity 불필요)
select identity(int, 1,1) "seq", *
into #ttt2
from #ttt
select * from #ttt2
/*
seq a b
---- ---- ----
1 2000 45
2 2001 55
3 2002 65
4 2005 75
*/
-- middle set?(요건 그냥 해본거)
select seq,
case when seq = 1 then a else null end "c1"
, case when seq = 2 then a else null end "c2"
, case when seq = 3 then a else null end "c3"
, case when seq = 4 then a else null end "c4"
, case when seq = 1 then b else null end "c5"
, case when seq = 2 then b else null end "c6"
, case when seq = 3 then b else null end "c7"
, case when seq = 4 then b else null end "c8"
from #ttt2
/*
seq c1 c2 c3 c4 c5 c6 c7 c8
---- ---- ---- ---- ---- ---- ---- ---- ----
1 2000 NULL NULL NULL 45 NULL NULL NULL
2 NULL 2001 NULL NULL NULL 55 NULL NULL
3 NULL NULL 2002 NULL NULL NULL 65 NULL
4 NULL NULL NULL 2005 NULL NULL NULL 75
*/
-- middle set
select seq, x
, case when seq = 1 and x = 1 then a
when seq = 1 and x = 2 then b
else null end "c1"
, case when seq = 2 and x = 1 then a
when seq = 2 and x = 2 then b
else null end "c2"
, case when seq = 3 and x = 1 then a
when seq = 3 and x = 2 then b
else null end "c3"
, case when seq = 4 and x = 1 then a
when seq = 4 and x = 2 then b
else null end "c4"
from #ttt2 a
cross join (select 1 "x" union all
select 2
) b
order by seq, x
-- result set
select x, max(c.c1) "c1", max(c.c2) "c2", max(c.c3) "c3", max(c.c4) "c4"
from
( select seq, x
, case when seq = 1 and x = 1 then a
when seq = 1 and x = 2 then b
else null end "c1"
, case when seq = 2 and x = 1 then a
when seq = 2 and x = 2 then b
else null end "c2"
, case when seq = 3 and x = 1 then a
when seq = 3 and x = 2 then b
else null end "c3"
, case when seq = 4 and x = 1 then a
when seq = 4 and x = 2 then b
else null end "c4"
from #ttt2 a
cross join (select 1 "x" union all
select 2
) b
) c
group by x -- 요거는 서브쿼리 안에서 그룹핑해도 되겠당...
/*
x c1 c2 c3 c4
-- ----- ----- ----- -----
1 2000 2001 2002 2005
2 45 55 65 75
*/
/******************************************************
** Case 5 : 함수로 부터 N X 2로 리턴되는 결과 Pivoting
******************************************************/
-- 함수 생성
if object_id('fn_parse_str') is not null
drop function fn_parse_str
go
create function fn_parse_str
( @str varchar(1000)
)
returns @retTable table ( seq int identity(1,1)
, col varchar(20))
as
begin
declare @len int
set @len = len(@str)
while (@len > 0)
begin
if charindex('/',@str) = 0
begin
insert into @retTable values (@str)
break
end
else
begin
insert into @retTable values (left(@str,charindex('/',@str)-1))
set @str = right(@str, @len-charindex('/',@str))
set @len = len(@str)
end
end
return
end
go
-- 일반 함수 리턴
select seq, col "a1"
from dbo.fn_parse_str('공개/등록/공고/실용/공표')
union all
select seq, col "a2"
from dbo.fn_parse_str('1000/900/800/700/600')
union all
select seq, col "a3"
from dbo.fn_parse_str('1000/900/800/700/600')
/*
seq a1
---- ----
1 공개
2 등록
3 공고
4 실용
5 공표
1 1000
2 900
3 800
4 700
5 600
1 1000
2 900
3 800
4 700
5 600
*/
-- 함수 리턴의 Pivoting
select max(a1) "공보", max(a2) "데이터건수", max(a3) "원문건수"
from
(
select seq, col "a1", null "a2", null "a3"
from dbo.fn_parse_str('공개/등록/공고/실용/공표')
union all
select seq, null "a1", col "a2", null "a3"
from dbo.fn_parse_str('1000/900/800/700/600')
union all
select seq, null "a1", null "a2", col "a3"
from dbo.fn_parse_str('1000/900/800/700/600')
) aa
group by aa.seq
/*
공보 데이터건수 원문건수
---- ---------- --------
공개 1000 1000
등록 900 900
공고 800 800
실용 700 700
공표 600 600
*/
/********************************************************************
** Case 6 : 특정 필드 여러 값(rows)을 하나의 연결된 값으로 변경하기
********************************************************************/
--테이블 및 데이터 생성
CREATE TABLE #applicant
( wkey CHAR(16)
, ord INT
, names VARCHAR(10)
);
GO
INSERT #applicant
SELECT 'EP0000000000A1P', 1, '김재원' UNION ALL
SELECT 'EP0000000000A1P', 2, '최보현' UNION ALL
SELECT 'EP0000000000A1P', 3, '정명훈' UNION ALL
SELECT 'EP0000000000A1P', 4, '최명환' UNION ALL
SELECT 'EP0000000000A1P', 5, '안인옥' UNION ALL
SELECT 'US0000000000B1P', 1, '정성현' UNION ALL
SELECT 'US0000000000B1P', 2, '성재모' UNION ALL
SELECT 'US0000000000B1P', 3, '이규태';
GO
SELECT * FROM #applicant;
GO
/*
wkey ord names
---------------- ----------- ----------
EP0000000000A1P 1 김재원
EP0000000000A1P 2 최보현
EP0000000000A1P 3 정명훈
EP0000000000A1P 4 최명환
EP0000000000A1P 5 안인옥
US0000000000B1P 1 정성현
US0000000000B1P 2 성재모
US0000000000B1P 3 이규태
*/
-- 방법 1) 엽기 쿼리 활용(Ref.sqler.pe.kr)
DECLARE @rtnString varchar(100);
SET @rtnString = ''
SELECT @rtnString = @rtnString + names + ','
FROM #applicant
WHERE wkey = 'EP0000000000A1P';
SELECT LEFT(@rtnString, LEN(@rtnString)-1) "출원인";
GO
/*
출원인
----------------------------------------
김재원,최보현,정명훈,최명환,안인옥
*/
-- 방법 2) XML FOR PATH활용
SELECT DISTINCT wkey
, STUFF((SELECT ',' + names AS [text()]
FROM #applicant b
WHERE b.wkey = a.wkey
FOR XML PATH('')),1,1,'') AS 출원인
FROM #applicant a