在一个单元格中返回多行

So I've looked around and seen the XML trick and the Variable trick, and neither really made enough sense to me to implement. What I have is a table with 4 Columns, The first is a unique identifier, the second is a relation to a different table, the third is varbinary(max), the last is a string. I want to combine columns three and four over column two. Is this possible?

所以我环顾四周,看到了XML技巧和变量技巧,而且我没有真正理解我的实施。我所拥有的是一个包含4列的表,第一个是唯一标识符,第二个是与不同表的关系,第三个是varbinary(max),最后一个是字符串。我想在第二列上合并第三列和第四列。这可能吗?

Example of Data:

数据示例:

| FileId  |  UniqueI1  |  BinaryData  | FileName |
|---------+------------+--------------+----------|
|   1     |     1      |    <byte>    | asp.jpg  |
|   2     |     1      |    <byte>    | asp1.jpg |
|   3     |     2      |    <byte>    | asp2.jpg |
|   4     |     2      |    <byte>    | asp3.jpg |
|   5     |     2      |    <byte>    | asp4.jpg |

Preferred Output:

|  UniqueI1  |          BinaryData          |          FileName            |
|------------+------------------------------+------------------------------|
|     1      |    <byte>, <byte>            | asp.jpg, asp1.jpg            |
|     2      |    <byte>, <byte>, <byte>    | asp2.jpg, asp3.jpg, asp4.jpg |    

I appreciate any help you may be able to provide me.

我感谢您提供给我的任何帮助。

2 个解决方案

#1

Sounds like you're trying to group your data and aggregate the BinaryData and FileName columns by concatenating their values.

听起来您正在尝试对数据进行分组,并通过连接它们的值来聚合BinaryData和FileName列。

There are no built-in aggregates for concatenation in t-sql, but there are a couple of ways to reach the same results.

在t-sql中没有用于连接的内置聚合,但有几种方法可以达到相同的结果。

In my opinion, by far the easiest way is to write a custom aggregate in c# leveraging the CLR. But it can also be done using STUFF or XML. You should have a look at Does T-SQL have an aggregate function to concatenate strings?

在我看来,到目前为止最简单的方法是在c#中利用CLR编写自定义聚合。但它也可以使用STUFF或XML完成。您应该看看T-SQL是否具有连接字符串的聚合函数?

#2

Try this:

DECLARE @t TABLE
    (
      FileID INT ,
      UniqueID INT ,
      Data VARBINARY(100) ,
      FileName VARCHAR(10)
    )

INSERT  INTO @t
VALUES  ( 1, 1, 1, 'asp.jpg' ),
        ( 2, 1, 2, 'asp1.jpg' ),
        ( 3, 2, 3, 'asp2.jpg' ),
        ( 4, 2, 4, 'asp3.jpg' ),
        ( 5, 2, 5, 'asp4.jpg' )


SELECT  UniqueID ,
        MAX(ca.data) AS Data,
        MAX(ca.name) AS Name
FROM    @t t1
        CROSS APPLY ( SELECT    STUFF(
                   (SELECT  ', ' + CONVERT(VARCHAR(MAX), t2.Data, 2)
                    FROM    @t t2
                    WHERE   t1.UniqueID = t2.UniqueID
                    ORDER BY FileID                  
                                FOR   XML PATH('') ,
                                          TYPE
                   ).value('.', 'varchar(max)'), 1, 2, '') AS DATA ,
                                STUFF(
                   (SELECT  ', ' + t2.FileName
                    FROM    @t t2
                    WHERE   t1.UniqueID = t2.UniqueID
                    ORDER BY FileID                  
                                FOR   XML PATH('') ,
                                          TYPE
                   ).value('.', 'varchar(max)'), 1, 2, '') AS NAME
                    ) ca
GROUP BY UniqueID

Output:

UniqueID    Data                            Name
1           00000001, 00000002              asp.jpg, asp1.jpg
2           00000003, 00000004, 00000005    asp2.jpg, asp3.jpg, asp4.jpg

For pivoting:

WITH    cte
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( PARTITION BY UniqueID ORDER BY FileID ) AS rn
               FROM     @t
             )
    SELECT  c.UniqueID ,
            ca1.[1] AS Data1 ,
            ca1.[2] AS Data2 ,
            ca1.[3] AS Data3 ,
            ca2.[1] AS File1 ,
            ca2.[2] AS File2 ,
            ca2.[3] AS File3
    FROM    cte c
            CROSS APPLY ( SELECT    *
                          FROM      ( SELECT    UniqueID ,
                                                rn ,
                                                Data
                                      FROM      cte ci
                                      WHERE     ci.UniqueID = c.UniqueID
                                    ) t PIVOT( MAX(Data) FOR rn IN ( [1], [2], [3] ) ) p
                        ) ca1    
            CROSS APPLY ( SELECT    *
                          FROM      ( SELECT    UniqueID ,
                                                rn ,
                                                FileName
                                      FROM      cte ci
                                      WHERE     ci.UniqueID = c.UniqueID
                                    ) t PIVOT( MAX(FileName) FOR rn IN ( [1], [2], [3] ) ) p
                        ) ca2
    GROUP BY c.UniqueID, ca1.[1], ca1.[2], ca1.[3], ca2.[1], ca2.[2], ca2.[3]

Output:

UniqueID    Data1       Data2       Data3       File1     File2     File3
1           0x00000001  0x00000002  NULL        asp.jpg   asp1.jpg  NULL
2           0x00000003  0x00000004  0x00000005  asp2.jpg  asp3.jpg  asp4.jpg

You can change this to dynamic query if you don't want to manually add additional files.

如果您不想手动添加其他文件,可以将其更改为动态查询。