Search code examples
sqlsql-serverstringdistinctaggregate-functions

How to have all the columns of a table by doing a disctint on a single one in with SQL Server?

I have a table from my SQL Server database composed of a stylecode column and an image:

stylecode   images

CTWAI4-RC   https://dwk1ydkfsczz3.cloudfront.net/CTWAI4-3589-1jpg-90da2f9b-d942-49f5-85df-a5fae6a76934.jpg
CTWAI4-RC   https://dwk1ydkfsczz3.cloudfront.net/CTWAI4-3589-1jpg-90da2f9b-d942-4785-85df-a5fae6a76934.jpg
WSM6HW-RC   https://dwk1ydkfsczz3.cloudfront.net/WSM6HW-0090-1jpg-998a79f1-6ed9-4610-b30d-113745e37c59.jpg
WSM6HW-RC   https://dwk1ydkfsczz3.cloudfront.net/WSM6HW-0090-1jpg-998a7781-6ed9-4610-b30d-113745e37c59.jpg
WLY8OW-RC   https://dwk1ydkfsczz3.cloudfront.net//WLY8OW-0026-1jpg-b526d225-5ae5-44fc-b78b-d755a3d94ff6.jpg
WLY8OW-RC   https://dwk1ydkfsczz3.cloudfront.net/WLY8OW-1651-1jpg-0051b696-d786-4864-9466-75f90b96fa33.jpg

As you can see I have several times the same style, I would like to make a query on my table to have only the same stylecode and the address of the corresponding images.

So I made a :

select distinct stylecode from dbo.Y2_Images;

Which returns all the unique stylecode but I would like to have next to it the values of the corresponding images,

I made a

select * from dbo.Y2_Images group by stylecode;

but my error is:

Column 'dbo.Y2_Images.images' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How to make the two columns have the same values by sorting the unique values of the code style?

Solution

  • There is more that one way to understand your question.

    If you want distinct style/images tuples, then:

    select distinct stylecode, images from dbo.Y2_images

    If you want just one image per style, even if there are several distinct values, use an aggregate function such as max() or min():

    select sylecode, max(images) as images from dbo.Y2_images group by sylecode

    If you want all distinct images in a single column when there are several, use string aggregation:

    select stylecode, stringagg(distinct images, ',') as images from db.Y2_images group by stylecode