Bob Zeller
I have a question about this fiddle:
<>https://dbfiddle.uk?rdbms=sqlserver_2019&fiddle=afedf644bcd4674cb2115c3720abf7d2
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)*