I have a table as below:

I want the result to be like below table:

I don't want to use UNION for this.

CodePudding user response：

First each row converted into two sets then apply conditional CASE statement for getting desired result.

-- SQL Server
SELECT t.ItemName
     , CASE WHEN p.id = 1 THEN [1mth_presale_cost] ELSE [1mth_postsale_cost] END  "1mth_cost"
     , CASE WHEN p.id = 1 THEN [2mth_presale_cost] ELSE [2mth_postsale_cost] END  "2mth_cost"
FROM test t
CROSS JOIN (SELECT 1 id UNION SELECT 2 id) p
ORDER BY t.ItemName, p.id

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3e1fdaf829c39554941c068b0c8a3e04

CodePudding user response：

You want to unpivot the data. In SQL Server, you can use cross apply:

select t.ItemName, v.1mth_cost, v.2mth_cost
from t cross apply
     (values (t.1mth_presale_cost, t.2mth_presale_cost, 'presale'),
             (t.1mth_postsale_cost, t.2mth_postsale_cost, 'postsale')
     ) v(1mth_cost, 2mth_cost, which);

Note that this you can also include which to see which of the columns is being used for each row.