How to add a category column in SQL and resize the records by creating views in SQL

Question

I need to create SQL Views which will convert the current table into the desired format mentioned below

I have introduce a new category column and reshape the columns accordingly.

Curent table layout:

     Date  | A flows| B flows | C flows | A yield | B Yield | C Yield | A Spread |  B Spread |  C Spread
    ----------------------------------------------------------------------------------------------------
    1/1/17 |10      | 20      |30       |   5     |10       |15       | 1        |  2        |3   

Desired View layout:

         Date   Category    flows   yield   Spread
         1/1/17     A       10       5       1
         1/1/17     B       20       10      2
         1/1/17     C       30       15      3

Can anyone help create a SQL View which will categorize existing columns under 3 categories.

Thanks!

Answer 1

I would use cross apply:

select v.*
from t cross apply
     (values (date, 'A', A_Flows, A_yield, A_spread),
             (date, 'B', B_Flows, B_yield, B_spread),
             (date, 'C', C_Flows, C_yield, C_spread)
      ) v(date, category, flows, yield, spread);

You can put create view as before the query to create a view.