Select distinct values across columns

Question

I'm trying to create a query that returns the distinct values across 4 columns in a single row. My where clause will limit the depth of the results to just one row but keep the data separated into columns. For example

SELECT color1, color2, color3, color4 FROM art WHERE id = 1

would return (in columns)

|Red |Blue |Blue |Green |

But I want only the distinct values

|Red |Blue |Green | |

Is there such a thing as using an unpivot and then pivot back to columns?

Answer 1

One option is to UNPIVOT your data and then PIVOT via a dense_rank()

Example

Declare @YourTable Table ([ID] varchar(50),[Color1] varchar(50),[Color2] varchar(50),[Color3] varchar(50),[Color4] varchar(50))
Insert Into @YourTable Values 
  (1,'Red','Blue','Blue','Green')

Select *
 From  (
        Select A.ID
              ,B.Item
              ,Col = concat('Color',dense_rank() over (partition by ID order by Item) )
         from @YourTable A
         Cross Apply ( values (color1)
                             ,(color2)
                             ,(color3)
                             ,(color4)
                     ) B(Item)
         Where ID=1
       ) src
 Pivot (max(item) for Col in (Color1,Color2,Color3,Color4) ) pvt

Returns

ID  Color1  Color2  Color3  Color4
1   Blue    Green   Red     NULL

Note: The Where ID=1 is optional