Return Multiple Rows in One Cell

Question

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?

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.

possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — Pரதீப், May 11 '15 at 14:59

score 1 · Answer 1 · edited May 23 '17 at 11:58

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

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

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?

Giorgi Nakeuri · Answer 2 · 2015-05-11T16:23:56.637

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.

While this most certainly does work, the problem is that I still need the data in byte form for my web application. I am using a telerik radgrid and I just wanted all the files to be in the same row as the Parent record it's attached to, as opposed to populating a record for each individual file. My users need to be able to click the document name and be able to open/download the file... Perhaps I should move this over to questions about radgrid data population instead. — Matthew Detweiler, May 11 '15 at 15:58

Return Multiple Rows in One Cell

2 Answers2