MS Sql Server Pivot two columns

Question

I can not seem to find an example of this type of pivot if it is possible.

+---------+---------+
| column1 | column2 |
+---------+---------+
|      10 | A       |
|      20 | B       |
|      30 | C       |
+---------+---------+

I would like my query to return

+----+----+----+
| A  | B  | C  |
+----+----+----+
| 10 | 20 | 30 |
+----+----+----+

I would like to do it without creating a view or dynamic table.

I have been trying the pivot option but I am having a problem understanding how to use dynamic values from the query. My query is:

 Select column1, column3 + '_' + column4 as column2 
 from table1 

Now I want to use the column2 values as columns names with the associated column1 values.

Answer 1

You can use the PIVOT function:

select A, B, C
from yourtable
pivot
(
  max(column1)   -- column values
  for column2 in (A, B, C)  -- new column names
) p;

If you have dynamic columns then you will have to use dynamic SQL and implement it inside of a stored procedure. The code would be similar to:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

-- create the list of columns for the pivot
select @cols = STUFF((SELECT ',' + QUOTENAME(column2) 
                    from 
                    (
                       select column3 + '_' + column4 as column2 
                       from table1
                     ) d
                     group by column2
                     order by column2
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' 
            from 
            (
               Select column1, column3 + ''_'' + column4 as column2 
               from table1
            ) x
            pivot 
            (
                max(column1)
                for column2 in (' + @cols + ')
            ) p '

exec sp_executesql @query;

Answer 2

select case when column2 = 'A' then column1 end as A,
       case when column2 = 'B' then column1 end as B,
       case when column2 = 'C' then column1 end as C
from your_table