CODEWITHSUNDEEP
sql-servert-sqlpivotunpivot
masfenix
masfenix

Reputation: 8006

SQL Pivoting a column

suppose I have a temp table created and the structure/data looks like this

  Type        Lang    N_cnt        Pcnt      T1      T2       T3      T4
============================================================================
ClassroomA      1      165897      1232     6439    1135     4516     1756
ClassroomA      2      175127      1254     6439    1135     1285     1953
ClassroomB      1      179515      1284     6439    1762     3945     1957
ClassroomB      2      159683      2041     6439    1575     4745     1955

I wanna pivot the T1, T2, T3, T4 columns such that I get this result

   Type       SubType   Lang      N_cnt            P_cnt   
==============================================================
ClassroomA      NULL     1       165897          1232  
NULL            "T1"     1   6439/165897     6439/1232       
NULL            "T2"     1       *calculation*   *calculation*
NULL            "T3"     1       *calculation*   *calculation*
NULL            "T4"     1       *calculation*   *calculation*
ClassroomA      NULL     2       175127          1254  
NULL            "T1"     2       6439/175127     6439/1254       
NULL            "T2"     2       *calculation*   *calculation*
NULL            "T3"     2       *calculation*   *calculation*
NULL            "T4"     2       *calculation*   *calculation*

Note that where it says calculation thats where I will be taking the value of T{x} and doing some calucation with it (I do an example for the first T1). I would also like to add that in reality I have about ten over these T variables.

Any ideas how to pivot the columns?

Upvotes: 0

Views: 144

Answers (1)

Taryn
Taryn

Reputation: 247810

You can use something similar to this:

select type, 
  case when subtype = 'start' then null else subtype end subtype,
  lang, 
  case when subtype = 'start' then n_cnt else value/(n_cnt * 1.0) end n_cnt,
  case when subtype = 'start' then pcnt else value/(pcnt * 1.0) end pcnt
from
(
  select type, lang, n_cnt, pcnt, 
    T1, T2, T3, T4, 0 as start
  from table1
) x
unpivot 
(
  value 
  for subtype in (T1, T2, T3, T4, start)
) un
order by type, lang, subtype;

See SQL Fiddle with Demo

Upvotes: 3

Related Questions