반응형

/***************************************************************************************
-- 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;

-- & -> &amp로 바뀌는 현상 제거
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";


반응형

+ Recent posts