合并有数据的列

BOM数据排序及分级显示

--示例数据:
CREATETABLEt1(
Namevarchar(20),
Col1varchar(2),
Col2varchar(2),
Col3varchar(2))
INSERTt1(Name,Col1,Col2,Col3)
SELECT'A','1','',''UNIONALL
SELECT'A','2','',''UNIONALL
SELECT'A','3','',''UNIONALL
SELECT'A','','4',''UNIONALL
SELECT'A','','5',''UNIONALL
SELECT'A','','6',''UNIONALL
SELECT'A','','7',''UNIONALL
SELECT'A','','','8'UNIONALL
SELECT'A','','','9'UNIONALL
SELECT'A','','','0'UNIONALL
SELECT'B','11','',''UNIONALL
SELECT'B','12','',''UNIONALL
SELECT'B','13','',''UNIONALL
SELECT'B','14','',''UNIONALL
SELECT'B','','15',''UNIONALL
SELECT'B','','16',''UNIONALL
SELECT'B','','17',''UNIONALL
SELECT'B','','','18'UNIONALL
SELECT'B','','','19'UNIONALL
SELECT'B','','','10'
GO

问题描述:
表t1中的列数不定，固定有一个Name列，大于1个的Coln列，列名为Col1~Coln，要求根据Name分组，合并各Coln列中有值的数据。对于示例数据，要求结果如下：

NameCol1Col2Col3
---------- --------------------------
A 1 4 0
A 258
A 369
A 7
B1115 10
B 121618
B 131719
B14

（所影响的行数为8行）

--处理代码
DECLARE@svarchar(8000),@iint,@fdvarchar(8000),@Namevarchar(8000)
SELECT@s='',@i=97,@fd='',@name='a.name'
SELECT@i=@i+1,
@s=@s+'
FULLJOIN(
SELECTName,'+QUOTENAME(Name)+',
ID=(SELECTCOUNT(*)FROMt1
WHEREName=a.Name
AND'+QUOTENAME(Name)+'>''''
AND'+QUOTENAME(Name)+'<=a.'+QUOTENAME(Name)+')
FROMt1a
WHERE'+QUOTENAME(Name)+'>''''
)'+CHAR(@i)+'ONa.Name='+CHAR(@i)+'.NameANDa.ID='+CHAR(@i)+'.ID',
@fd=@fd+',
'+QUOTENAME(Name)+'=ISNULL('+CHAR(@i)+'.'+QUOTENAME(Name)+','''')',
@name='ISNULL('+CHAR(@i)+'.Name,'+@name+')'
FROMsyscolumns
WHEREID=OBJECT_ID(N't1')
ANDName<>'Col1'
ANDNameLIKE'Col%'
EXEC('
SELECTName='+@name+',
Col1=ISNULL(A.Col1,'''')'+@fd+'
FROM(
SELECTName,Col1,
ID=(SELECTCOUNT(*)FROMt1
WHEREName=a.Name
ANDCol1>''''
ANDCol1<=a.Col1)
FROMt1a
WHERECol1>''''
)a'+@s)
GO

原帖地址