iT邦幫忙

9

【SQL分享】把多個欄位值轉成單個Unique的欄位

sql sqlserver
暐翰 2018-06-02 10:25:10 ‧ 10952 瀏覽

昨天回答一個問題，這邊做整理筆記
sql - Combining Claim lines For Unique Values - Stack Overflow

發問者QQ(化名):
QQ資料庫版本是SQL Server 2012

資料結構如:

Claim Number    EX1 Ex2 Ex3 Ex4 Ex5 Ex6
123456789123    LP  DU  1O            
123456789123    LP  DU  1O                   
123456789123    LP  IK  D3  AP  1O    
123456789123    LP  DU  1O            
123456789123    LP  IK  D3  AP  1O

QQ想要以Claim Number為組得出Ex的不重複值

Claim Number    Codes
123456789123    LP,DP,AP,1O,DU,IK,D3

我的一開始直覺作法是:
【第一步】先使用union組合並排除重複Ex1~Ex6的值，並組成單一Ex欄位
【第二步】用STUFF FOR XML 以Claim Number為組，組合出Ex + ,的值

with CTE as (
    select distinct [Claim_Number], [EX1] ex from TestTable
    union select [Claim_Number], [EX2] ex from TestTable
    union select [Claim_Number], [EX3] ex from TestTable
    union select [Claim_Number], [EX4] ex from TestTable
    union select [Claim_Number], [EX5] ex from TestTable
    union select [Claim_Number], [EX6] ex from TestTable
)
select [Claim_Number],
  Codes = STUFF((SELECT  ','+cast([ex] AS VARCHAR ) FROM  CTE t WHERE  C.[Claim_Number]  = t.[Claim_Number]  FOR XML PATH('')),1,1,'') 
from CTE C
group by Claim_Number

SQL Fiddle DEMO LINK

接著看到Gordon Linoff大的更精簡作法
CTE可以改成用Cross Apply Values方式

with CTE as (
  select distinct Claim_Number, ex
  from TestTable 
  cross apply (values (ex1), (ex2), (ex3), (ex4), (ex5)) v(ex)
)
select [Claim_Number],
  Codes = STUFF((SELECT  ','+cast([ex] AS VARCHAR ) FROM  CTE t WHERE  C.[Claim_Number]  = t.[Claim_Number]  FOR XML PATH('')),1,1,'') 
from CTE C
group by Claim_Number

附註:
在最新版SQL Server 2017可以使用STRING_AGG取代
STUFF FOR XML (也建議使用，因為簡單使用，效能更好)

select [Claim_Number],STRING_AGG ( ex, ',')
from (
    select [Claim_Number], [EX1] ex from TestTable
    union select [Claim_Number], [EX2] ex from TestTable
    union select [Claim_Number], [EX3] ex from TestTable
    union select [Claim_Number], [EX4] ex from TestTable
    union select [Claim_Number], [EX5] ex from TestTable
    union select [Claim_Number], [EX6] ex from TestTable
) T
group by Claim_Number

SQL Fiddle DEMO LINK

延伸閱讀:

假如有其他資料庫作法或是更好的方式
大大們都可以提出、討論。


圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

3 則留言

3
一級屠豬士
iT邦新手 2 級 ‧ 2018-06-03 02:22:41 
把資料加一組,ex6也放點資料.

create table ithelp180602 (
  id serial not null primary key
, grp int not null
, ex1 char(2)
, ex2 char(2)
, ex3 char(2)
, ex4 char(2)
, ex5 char(2)
, ex6 char(2) 
);

insert into ithelp180602 values
(default, 1, 'LP','DU', '1O', Null, Null, Null), 
(default, 1, 'LP','DU', '1O', Null, Null, Null), 
(default, 1, 'LP','IK', 'D3', 'AP', '1O', Null), 
(default, 1, 'LP','DU', '1O', Null, Null, Null),
(default, 1, 'LP','IK', 'D3', 'AP', '1O', Null),
(default, 2, 'LP','DU', '1O', Null, Null, 'XP'),
(default, 2, 'LP','IK', 'D3', 'AP', '1O', 'XP');


--------------

select grp
     , array_agg(DISTINCT ex1) FILTER (WHERE ex1 IS NOT NULL)
    || array_agg(DISTINCT ex2) FILTER (WHERE ex2 IS NOT NULL)
    || array_agg(DISTINCT ex3) FILTER (WHERE ex3 IS NOT NULL)
    || array_agg(DISTINCT ex4) FILTER (WHERE ex4 IS NOT NULL)
    || array_agg(DISTINCT ex5) FILTER (WHERE ex5 IS NOT NULL)
    || array_agg(DISTINCT ex6) FILTER (WHERE ex6 IS NOT NULL)
  from ithelp180602
 group by grp;
 
 grp |         ?column?          
-----+---------------------------
   1 | {LP,DU,IK,1O,D3,AP,1O}
   2 | {LP,DU,IK,1O,D3,AP,1O,XP}
(2 筆資料列)
 
-----------
-- 上面的array 還是會有重複的,我們可以建立funcion, 
-- 利用 Postgresql 原本就有的unnest() 展開,
-- 再使用 distinct 語法排除後,再組成array.

CREATE OR REPLACE FUNCTION array_distinct(anyarray)
RETURNS anyarray AS $$
  SELECT ARRAY(SELECT DISTINCT unnest($1))
$$ LANGUAGE sql;

---------- 
select grp
     , array_distinct(
       array_agg(DISTINCT ex1) FILTER (WHERE ex1 IS NOT NULL)
    || array_agg(DISTINCT ex2) FILTER (WHERE ex2 IS NOT NULL)
    || array_agg(DISTINCT ex3) FILTER (WHERE ex3 IS NOT NULL)
    || array_agg(DISTINCT ex4) FILTER (WHERE ex4 IS NOT NULL)
    || array_agg(DISTINCT ex5) FILTER (WHERE ex5 IS NOT NULL)
    || array_agg(DISTINCT ex6) FILTER (WHERE ex6 IS NOT NULL)
       ) as code
  from ithelp180602
 group by grp;
 
 grp |          code          
-----+------------------------
   1 | {DU,IK,D3,AP,LP,1O}
   2 | {DU,IK,D3,AP,XP,LP,1O}
(2 筆資料列)

利用array功能,並適當建立函數,搭配使用.不用 union 好幾次.
使用PostgreSQL輕鬆愉快.

暐翰 iT邦大師 1 級 ‧ 2018-06-03 17:20:44 檢舉

/images/emoticon/emoticon12.gif
謝謝一級屠豬士大大!
類似array_agg的簡便語法、SQL Server 2017才支持

PostgreSQL在函數上面的支持真的很強

假如想線上測試大大SQL的邦友可以點 SQL Fiddle Demo Link

登入發表回應
2
小碼農米爾
iT邦高手 1 級 ‧ 2018-06-03 17:14:20

感謝大大分享，第一次看到 Cross Apply Values 和 STRING_AGG，又學到一課。
/images/emoticon/emoticon41.gif

接著小弟在這裡分享一下 UNPIVOT 的寫法，
首先用 UNPIVOT 將資料橫轉直，並把空白和重複的資料過濾。

;WITH PV AS
(
	SELECT DISTINCT PV.ClaimNumber, PV.EX
	FROM 
	(
		SELECT ClaimNumber, EX1, EX2, EX3, EX4, EX5, EX6
		FROM @Temp
	) AS T
	UNPIVOT
	(
		EX FOR Title IN (EX1, EX2, EX3, EX4, EX5, EX6)
	) AS PV
	WHERE PV.EX<>''
)

接著和大大一樣用 STUFF + FOR XML PATH 將資料分組後用逗號串接，這樣就完成了。

SELECT DISTINCT B.ClaimNumber,
	   STUFF((
	       SELECT ','+A.EX
	       FROM PV AS A
	       WHERE A.ClaimNumber=B.ClaimNumber
	       FOR XML PATH('')
	   ), 1, 1, '') AS EX 
FROM PV AS B

結果

ClaimNumber EX
123456789123 1O,AP,D3,DP,DU,IK,LP

不過發現 EX 的順序亂掉了，雖然 QQ 沒有要求順序不能亂，不過還是想試試看，能不能讓 EX 照著原始資料出現的順序排序。

首先修改第一部分的 CTE，增加 RID 和 EXID，
RID 用來模擬資料表的主鍵 (雖然這裡排序後順序可能會亂掉，不過先假裝不會)。/images/emoticon/emoticon39.gif
EXID 則是用來取代原來的 DISTINCT，
因為 DISTINCT 會導致資料的順序亂掉，
這裡的邏輯是先將資料以 ClaimNumber, EX 分組，
然後照著原始資料的順序 RID, Title 排序並加上編號，
接著取出編號等於一的資料，這樣就將重覆的資料去掉了，且順序不會亂。

;WITH PV AS
(
	SELECT *
	FROM
	(
		SELECT *,
		       ROW_NUMBER() OVER (PARTITION BY ClaimNumber, EX ORDER BY RID, Title) AS EXID
		FROM
		(
			SELECT *
			FROM 
			(
				SELECT ClaimNumber, EX1, EX2, EX3, EX4, EX5, EX6,
				       ROW_NUMBER() OVER (ORDER BY ClaimNumber) AS RID
				FROM @Temp
			) AS T
			UNPIVOT
			(
				EX FOR Title IN (EX1, EX2, EX3, EX4, EX5, EX6)
			) AS PV
			WHERE PV.EX<>''
		) AS PV
	) AS PV
	WHERE PV.EXID=1
)

第二部分會在串逗號前先將資料排序，保持 EX 原始的出現順序。

SELECT DISTINCT B.ClaimNumber,
	   STUFF((
	       SELECT ','+A.EX
	       FROM PV AS A
	       WHERE A.ClaimNumber=B.ClaimNumber
		   ORDER BY A.RID, A.Title
	       FOR XML PATH('')
	   ), 1, 1, '') AS EX 
FROM PV AS B

結果

ClaimNumber EX
123456789123 LP,DU,1O,IK,D3,AP,DP

測試資料

DECLARE @Temp TABLE
(
	ClaimNumber NVARCHAR(100),
	EX1 NVARCHAR(10),
	Ex2 NVARCHAR(10), 
	Ex3 NVARCHAR(10), 
	Ex4 NVARCHAR(10), 
	Ex5 NVARCHAR(10), 
	Ex6 NVARCHAR(10)
)

INSERT INTO @Temp
	(ClaimNumber, EX1, Ex2, Ex3, Ex4, Ex5, Ex6)
VALUES
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','IK','D3','AP','1O',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','IK','D3','AP','1O',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','IK','D3','AP','1O',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','IK','D3','AP','1O',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','IK','D3','AP','1O',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','IK','D3','AP','1O',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DP','AP','1O','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','',''),
    ('123456789123','LP','DU','1O','','','')
暐翰 iT邦大師 1 級 ‧ 2018-06-03 17:37:29 檢舉

/images/emoticon/emoticon12.gif
直接用行轉列好方法呢!

假如想線上測試fysh711426大大SQL可以點擊連結:SQL Fiddle Demo Link

登入發表回應
3
純真的人
iT邦大師 1 級 ‧ 2018-06-07 10:35:33

恩@@...我也用我自己的方式玩一下..

declare @Claim table(
	Number varchar(50)
	,Ex1 varchar(2)
	,Ex2 varchar(2)
	,Ex3 varchar(2)
	,Ex4 varchar(2)
	,Ex5 varchar(2)
	,Ex6 varchar(2)
)

insert into @Claim
values('123456789123','LP','DU','1O','','','')
,('123456789123','LP','DU','1O','','','')
,('123456789123','LP','IK','D3','AP','1O','')
,('123456789123','LP','DU','1O','','','')
,('123456789123','LP','IK','D3','AP','1O','')

select Number
,(
	select Ex1 + ','
	from ((
		select Ex1 from @Claim as a where a.Number = k.Number and Ex1 <> ''
	)union(
		select Ex2 from @Claim as a where a.Number = k.Number and Ex2 <> ''
	)union(
		select Ex3 from @Claim as a where a.Number = k.Number and Ex3 <> ''
	)union(
		select Ex4 from @Claim as a where a.Number = k.Number and Ex4 <> ''
	)union(
		select Ex5 from @Claim as a where a.Number = k.Number and Ex5 <> ''
	)union(
		select Ex6 from @Claim as a where a.Number = k.Number and Ex6 <> ''
	)) as k
	for xml path('')
) as Codes
from (
	select Number
	from @Claim
	group by Number
) as k

https://ithelp.ithome.com.tw/upload/images/20180607/20061369vMVdFOgtrR.png

暐翰 iT邦大師 1 級 ‧ 2018-06-10 18:58:42 檢舉

/images/emoticon/emoticon12.gif
大大這周又有新文章，歡迎來玩【SQL分享】 統計工廠設備每天運作狀況時間 - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天

純真的人 iT邦大師 1 級 ‧ 2018-06-10 19:53:12 檢舉

哈~

登入發表回應

我要留言

立即登入留言
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css react 程式設計 vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙