Store a dynamic list of images in 1 row

Question

I am joining these two tables and I would like to send a view of the Test with its image(s).

• Test

  • Name (VARCHAR(255))
  • TestId (INT)
  • StartTime (DATETIME2(7))
  • Status (VARCHAR(255))

• Images

  • ImageId (INT)
  • TestId (INT)
  • Image (VARBINARY(MAX))

Right now I am doing this:

SELECT Name, t.TestId, StartTime, Status, Image
FROM [Test] t
LEFT JOIN [Images] i ON i.TestId = t.TestId

What I don't like here is that I have multiple rows from the same test but with different images. Is there a convenient way to return only one row of each test with its image(s) ?

My images are not that heavy (max 100KB) and I don't have a lot of images per test (max 10). Here's what I think:

1. It might sound crazy but maybe within SQL server there is a way to convert the Images into a list of images as a VARBINARY(MAX)
2. I could have 10 columns (image1, image2...) in the view and fill them with the images if they exist (NOT NULL)

I am a bit stuck with these solutions as I don't know a lot about SQL, but I am still trying. Do you see another way to do it? If not, could you help me with useful advice on how to achieve 1. or 2.

Answer 1

What I don't like here is that I have multiple rows from the same test but with different images.

So what? Its just a few bytes repeated for each row, which is insignificant compared to a 100KB blob.

But you can retrieve the data in exactly the shape you want, if you have SQL Server convert it to JSON. varbinary(max) will be base64 encoded and adding all the JSON overhead this will be a bigger result size. But a query like

SELECT Name , 
       t.TestId , 
       StartTime , 
       Status ,  
       (select Image from Images where testid = t.testid for json path) Images
FROM [Test] t
for json path

will output data like

[
    {
        "Name": "Test1",
        "TestId": 1,
        "StartTime": "2020-04-22T18:15:47.9533333",
        "Status": "complete",
        "Images": [
            {
                "Image": "j2LYPy9Uy0Wbz0/qsPk0qo9i2D8vVMtFm89P6rD5NKqPYtg/L1TLRZvPT+qw+TSqj2LYPy9Uy0Wbz0/qsPk0qo9i2D8vVMtFm89P6rD5NKo="
            },
            {
                "Image": "j2LYPy9Uy0Wbz0/qsPk0qo9i2D8vVMtFm89P6rD5NKqPYtg/L1TLRZvPT+qw+TSqj2LYPy9Uy0Wbz0/qsPk0qo9i2D8vVMtFm89P6rD5NKo="
            }
        ]
    }
]

Answer 2

The second option can be implemented with row_number() and conditional aggregation, like so:

select 
    t.name,
    t.testid,
    t.starttime,
    t.status,
    max(case when i.rn =  1 then i.image end) image01,
    max(case when i.rn =  2 then i.image end) image02,
    ...
    max(case when i.rn = 10 then i.image end) image10
from test t
left join (
    select testid, image, row_number() over(partition by testid order by imageid) rn
    from images
) i on i.testid = t.testid
group by t.name, t.testid, t.starttime, t.status

You can also use outer apply, which might be more efficient:

select
    t.*,
    i.*
from test t
outer apply (
    select 
        max(case when rn =  1 then image end) image01,
        max(case when rn =  2 then image end) image02
        ...
        max(case when rn = 10 then image end) image10
    from (
        select image, row_number() over(order by imageid) rn
        from image i
        where i.testid = t.testid
    ) i
) i