0

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

1 Answer 1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.