/***************************************************************************************
-- Title : [2k] 멀티값을 싱글값으로 업데이트하는 예(전체 ROW 처리)
-- Reference : 웹 검색, dBRang.com
-- Key word : xml for path, 멀티값, 싱글값, pivot, unpivot, multi row single row crosstab
***************************************************************************************/
* dbrang.tistory.com/65 도 참조.
/*
-- 함수를 이용한 업데이트
*/
-- 멀티열의 단일열 결합 함수 만들기
IF OBJECT_ID('fn_paj_ap') IS NOT NULL
DROP FUNCTION dbo.fn_paj_ap
GO
CREATE FUNCTION dbo.fn_paj_ap(@wkey CHAR(16))
RETURNs VARCHAR(1200)
AS
BEGIN
DECLARE @ap VARCHAR(1200)
DECLARE @name VARCHAR(500)
DECLARE @return_ap VARCHAR(1200)
SET @ap = ''
SET @name = ''
DECLARE cur_ap CURSOR READ_ONLY
FOR
SELECT ap FROM jp..pajap2 WHERE wkey = @wkey AND ap IS NOT NULL
OPEN cur_ap
FETCH NEXT FROM cur_ap
INTO @name
WHILE @@fetch_status=0
BEGIN
SET @ap = @ap + @name + '↑↑'
FETCH NEXT FROM cur_ap
INTO @name
END
CLOSE cur_ap
DEALLOCATE cur_ap
IF (@ap IS NULL or @ap = '')
SET @return_ap = NULL
ELSE
BEGIN
SET @ap = left(@ap, len(@ap)-2)
SET @return_ap = @ap
END
RETURN @return_ap
END
-- 함수를 활용한 업데이트
UPDATE paj_saltlux_t
SET ap = dbo.fn_paj_ap(a.wkey)
FROM paj_saltlux_t a
/*
-- 단순 쿼리를 이용한 업데이트
-- 방법 1,2를 직접 또는 함수에 넣어 사용한다.
*/
--테이블 및 데이터 생성
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활용
-- 값에 NULL이 존재하는 경우 자동으로 없애고 나머지를 붙인다. 똑똑햐~!!
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;
SELECT DISTINCT wkey
, STUFF((SELECT ' / ' + names AS [text()]
FROM #applicant b
WHERE b.wkey = a.wkey
FOR XML PATH('')),1,2,'') AS 출원인
FROM #applicant a;
-- & -> &로 바뀌는 현상 제거
SELECT DISTINCT wkey
, STUFF(((SELECT ' / ' + names AS [text()]
FROM #applicant b
WHERE b.wkey = a.wkey
FOR XML PATH(''),type).value('.','VARCHAR(max)')
),1,2,''
) AS 출원인
FROM #applicant a;
-->> ROW하나로 전체를 머지할 때는 self join 없이 그냥 처리
select STUFF((SELECT ',' + ISNULL(CAST(skey as nvarchar(13)),'') AS [text()]
from
(
select rank, skey, ctry_num, doc_kind
from
(
select rank, skey, ctry_num, doc_kind
, row_number() over(partition by rank order by ctry_num asc, doc_kind asc) "seq"
from #result
) a
where seq = 1
) s
order by rank
FOR XML PATH('')), 1, 1, '') "skeys_ord";