MS SQL Server pivoting without aggregation

Question

I have this table:

   CREATE TABLE Sales (idx int, variable varchar(50), value varchar(50));
   INSERT into Sales (idx, variable, value) VALUES(1, 'column1', '34');
   INSERT into Sales (idx, variable, value) VALUES(1, 'column2', 'text value');
   INSERT into Sales (idx, variable, value) VALUES(1, 'column3', 'another text');

   INSERT into Sales (idx, variable, value) VALUES(2, 'column1', '14');
   INSERT into Sales (idx, variable, value) VALUES(2, 'column2', 'text value2');
   INSERT into Sales (idx, variable, value) VALUES(2, 'column3', 'another text2');

And I want to transform it into this:

   CREATE TABLE SalesPivoted (column1 varchar(50), column2 varchar(50), column3 varchar(50));

   INSERT INTO SalesPivoted (column1, column2, column3) VALUES ('34', 'text value', 'another text');
   INSERT INTO SalesPivoted (column1, column2, column3) VALUES ('14', 'text value2', 'another text2');

Maybe there is a way to do that by pivot expression but I don't need any aggregation here

Answer 1

For pivot you do need an aggregate function. But even with that, you can get the requested results.

SELECT pivot_table.column3,
       pivot_table.column2,
       pivot_table.column1
FROM
(SELECT idx, variable, value FROM dbo.Sales) t
PIVOT
(
    MIN(value)
    FOR variable IN (column1, column2, column3)
) AS pivot_table;