0

I want to transform the following table:

ID      Var1  Var2  Var3  Var4
112233  1     2     3     4
112233  5     6     7     8
112233  9     10    11    12

into something like the following

ID      VarName      Value
112233     Var1      1
112233     Var1      2
112233     Var1      3
112233     Var1      4
112233     Var2      5
112233     Var2      6
112233     Var2      7
112233     Var2      8
112233     Var3      9
112233     Var3      10
112233     Var3      11
112233     Var3      12

Is there an simple way to do it?

jw0ng
  • 91
  • 4
  • 11

2 Answers2

2

You can use apply :

select id, VarName, value
from table t cross apply
     ( values ('Var1', var1), ('Var2', var2), ('Var3', var3)
     ) tt (VarName, value)
order by VarName;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
2

Use unpivot:

demo

select id,varname, value from tablename
Unpivot
(
  value for varname in (var1, var2, var3,var4)

) as UnPvt
Fahmi
  • 37,315
  • 5
  • 22
  • 31