【SQL分享】把多个栏位值转成单个Unique的栏位

昨天回答一个问题，这边做整理笔记
sql - Combining Claim lines For Unique Values - Stack Overflow

发问者QQ(化名):
QQ资料库版本是SQL Server 2012

资料结构如:

Claim Number    EX1 Ex2 Ex3 Ex4 Ex5 Ex6123456789123    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    Codes123456789123    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 Cgroup 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 Cgroup 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) Tgroup by Claim_Number

SQL Fiddle DEMO LINK

---

延伸阅读:

有使用2017版本的大大，想了解STRING_AGG 跟 v.next的比较可以看这篇文章: SQL Server v.Next : STRING_AGG() performance - SQLPerformance.com

---

假如有其他资料库作法或是更好的方式
大大们都可以提出、讨论。