合并有数据的列

最新推荐文章于 2023-08-16 15:05:37 发布

原创最新推荐文章于 2023-08-16 15:05:37 发布 · 342 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#object #go

本文介绍了一种处理不确定数量的列数据的方法，并通过示例展示了如何将多个可能存在的Coln列数据按条件合并并展示。适用于需要对不固定列进行排序与分级显示的应用场景。

部署运行你感兴趣的模型镜像

BOM数据排序及分级显示

--示例数据:
CREATE TABLE t1(
    Name varchar(20),
    Col1 varchar(2),
    Col2 varchar(2),
    Col3 varchar(2))
INSERT t1(Name,Col1,Col2,Col3)
SELECT 'A','1','','' UNION ALL
SELECT 'A','2','','' UNION ALL
SELECT 'A','3','','' UNION ALL
SELECT 'A','','4','' UNION ALL
SELECT 'A','','5','' UNION ALL
SELECT 'A','','6','' UNION ALL
SELECT 'A','','7','' UNION ALL
SELECT 'A','','','8' UNION ALL
SELECT 'A','','','9' UNION ALL
SELECT 'A','','','0' UNION ALL
SELECT 'B','11','','' UNION ALL
SELECT 'B','12','','' UNION ALL
SELECT 'B','13','','' UNION ALL
SELECT 'B','14','','' UNION ALL
SELECT 'B','','15','' UNION ALL
SELECT 'B','','16','' UNION ALL
SELECT 'B','','17','' UNION ALL
SELECT 'B','','','18' UNION ALL
SELECT 'B','','','19' UNION ALL
SELECT 'B','','','10'
GO

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

Name    Col1    Col2    Col3
---------- --------- --------- --------
A            1          4           0
A            2          5           8
A            3          6           9
A                        7
B           11        15        10
B           12        16        18
B           13        17        19
B           14

（所影响的行数为 8 行）

--处理代码
DECLARE @s varchar(8000),@i int,@fd varchar(8000),@Name varchar(8000)
SELECT @s='',@i=97,@fd='',@name='a.name'
SELECT @i=@i+1,
    @s=@s+'
        FULL JOIN(
            SELECT Name,'+QUOTENAME(Name)+',
                ID=(SELECT COUNT(*) FROM t1
                    WHERE Name=a.Name
                        AND '+QUOTENAME(Name)+'>''''
                        AND '+QUOTENAME(Name)+'<=a.'+QUOTENAME(Name)+')
            FROM t1 a
            WHERE '+QUOTENAME(Name)+'>''''
        )'+CHAR(@i)+' ON a.Name='+CHAR(@i)+'.Name AND a.ID='+CHAR(@i)+'.ID',
    @fd=@fd+',
        '+QUOTENAME(Name)+'=ISNULL('+CHAR(@i)+'.'+QUOTENAME(Name)+','''')',
    @name='ISNULL('+CHAR(@i)+'.Name,'+@name+')'
    FROM syscolumns
    WHERE ID=OBJECT_ID(N't1')
        AND Name<>'Col1'
        AND Name LIKE 'Col%'
EXEC('
    SELECT Name='+@name+',
        Col1=ISNULL(A.Col1,'''')'+@fd+'
    FROM(
        SELECT Name,Col1,
            ID=(SELECT COUNT(*) FROM t1
                WHERE Name=a.Name
                    AND Col1>''''
                    AND Col1<=a.Col1)
    FROM t1 a
    WHERE Col1>''''
    )a'+@s)
GO