sql-server sql-server-2019
Bob Zeller
I have a question about this fiddle:


In my example, I would like to create a query that returns all the associated picture filenames for every product specified in columns rather than duplicate rows. Unfortunately, I am having little luck with SQL's PIVOT function. 

The syntax is very hard for me to get my head around. It's unclear why an aggregate function is required and how it goes about selecting values. 
Top Answer
Jack Douglas
You need to specify the actual values from the column you are pivoting, not just `[0],[1]…`:

>     SELECT * FROM src
>     PIVOT (
>       MIN(PictId)
>       FOR SeoFileName in ([strawberry],[strawberry_1],[pinapples],[banana],[banana_1],[orange]
>       )
>     ) As Pvt
>     GO
> Name         | Price | strawberry | strawberry_1 | pinapples | banana | banana_1 | orange
> :----------- | ----: | ---------: | -----------: | --------: | -----: | -------: | -----:
> Banana       |    10 |       *null* |         *null* |      *null* |      4 |        5 |   *null*
> Orange       |    11 |       *null* |         *null* |      *null* |   *null* |     *null* |      6
> Pineapples   |    10 |       *null* |         *null* |         3 |   *null* |     *null* |   *null*
> Strawberries |    11 |          1 |            2 |      *null* |   *null* |     *null* |   *null*

I wonder if you really just want a list of `seofilename` like so:

>     SELECT name, STRING_AGG(seofilename,',') seofilenames
>     FROM src
>     GROUP BY name;
>     GO
> name         | seofilenames           
> :----------- | :----------------------
> Banana       | banana,banana_1        
> Orange       | orange                 
> Pineapples   | pinapples              
> Strawberries | strawberry,strawberry_1

*db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=cb4316abc80393c5e928f3086f242cee)*
I am having difficulty understanding the SQL's pivot syntax
Andriy M
Oops, looks like you've got two accounts, and I've already forgotten which one I clicked to ping you. Using both now to make sure you are notified, see my questions above ^^^
Andriy M
Hey @Bob, it's not very clear what the output should look like, specifically what values you expect to be in columns `0`, `1`, `3`. Also, why would they be called `0`, `1`, `3` and not, say, `A`, `B`, `D`? Could you elaborate on that one too, please?
Bob Zeller
All four image columns must be generated regardless if there are any images.
Bob Zeller
My tables have 0..n images over n rows. I will need to transpose them to a max of 4 columns using numbered suffix column.  I only need to map the first 4 images and ignore the rest.  I am completely stumped here. 
Bob Zeller
There's a fixed number of columns, since the database only supports up to 4 URL images.
Bob Zeller
If you look at the very last query they have numbered columns. Under each column, there's a file name. Normally only the first column "MainImageUrl" will be filled out. If there's more than one image, then each subsequent URL is filled in its accompanying column. 
Bob Zeller
@Jack.  Sorry to bother you again. It turns out I confused myself about the nature of the issue.  Unfortunately, STRING_AGG won't work for me either.  I just updated the fiddle to reflect the issue I am having. 
Jack Douglas replying to Bob Zeller
You are welcome :)
Bob Zeller
@Jack  Thanks. Yup, the STRING_AGG is what I was looking for.  Pivot now makes a lot more sense. The columns are values instead of new column names.