I have a Microsoft SQL Server table that has three columns in it - one for Locations, one for the associated Images and one indicating whether a given image is meant be used as the main Image for a given location (this is done because historically multiple images were uploaded for each location and this column indiciated which image is the one that actually gets used).
Now we want to be able pick and assign a second image as a logo for our locations, resulting in a fourth column added to indiciate which image becomes that logo.
So I have a table that looks something like this:
+----------+----------+-----------------+-----------+
| filename | location | IsMainImage | IsLogo |
+----------+----------+-----------------+-----------+
| img1 | 10 | True | Null |
| img2 | 10 | Null | True |
| img3 | 10 | Null | Null |
| img4 | 20 | True | Null |
| img5 | 20 | NULL | True |
+----------+----------+-----------------+-----------+
My goal is to write a query that would return both img1 and img2 as different columns within the same row in my query, followed by img3 and img4 another row. From the table above I need the output to look like this:
+-----------+-------------+
| filename1 | filename2 |
+-----------+-------------+
| img1 | img2 |
| img4 | img5 |
+-----------+-------------+
Please note that my description is an oversimplication. I am modifying and SSIS package that is consumed by another proces that I cannot modify. This is the reason why I need the output in this format. What became Filename1 and Filename2 used to be the same file (logo was a resized version of the main image) and now I need to differentiate between the two.
It is crucial that only the columns flagged as IsMainImage show under filename1 and only the columns flagged under IsLogo show under filename2.
I would appreciate any help with this. Thank you!
Using Case expression:
SELECT max(CASE WHEN [IsMainImage] = 'True' then filename end) as filename1,
max(CASE WHEN [IsLogo] = 'True' then filename end) as filename2
from Table_testcase group by location;