Pivot or Unpivot a small table

Question

I'm having a hard time wrapping my head around pivot/unpivot - all examples I find online I think are more complex than I need.

Imagine a table as such:

CREATE TABLE Custom (ID tinyint identity, value nvarchar(20))
INSERT INTO Custom VALUES ('red')
INSERT INTO Custom VALUES ('green')
INSERT INTO Custom VALUES ('blue')

The table displays like

ID    VALUE
1     red
2     green
3     blue

I want the table to display like

COLOR1    COLOR2    COLOR3
red       green     blue

Is this possible with UNPIVOT?

Thanks!

Answer 1

Here is one way to generate the desired results with conditional aggregation:

select 
    max(case when id = 1 then value end) color1,
    max(case when id = 2 then value end) color2,
    max(case when id = 3 then value end) color3
from custom

If you don't have a sequencial id starting at 1, you can emulate it with row_number():

select
    max(case when rn = 1 then value end) color1,
    max(case when rn = 2 then value end) color2,
    max(case when rn = 3 then value end) color3
from (select value, row_number() over(order by id) rn from mytable)