Convert data from wide format to long format in SQL

Question

I have some data in the format:

VAR1 VAR2 Score1 Score2 Score3
A     B    1      2      3

I need to convert it into the format

VAR1 VAR2 VarName Value
 A     B  Score1   1
 A     B  Score2   2
 A     B  Score3   3

How can I do this in SQL?

Answer 1

Provided your score columns are fixed and you require no aggregation, you can use multiple SELECT and UNION ALL statements to generate the shape of data you requested. E.g.

SELECT [VAR1], [VAR2], [VarName] = 'Score1', [Value] = [Score1]
FROM [dbo].[UnknownMe]
UNION ALL
SELECT [VAR1], [VAR2], [VarName] = 'Score2', [Value] = [Score2]
FROM [dbo].[UnknownMe]
UNION ALL
SELECT [VAR1], [VAR2], [VarName] = 'Score3', [Value] = [Score3]
FROM [dbo].[UnknownMe]

SQL Fiddle: http://sqlfiddle.com/#!6/f54b2/4/0

Answer 2

In hive, you could use the named_struct function, the array function, and the explode function in conjunction with the LATERAL VIEW construct

SELECT VAR1, VAR2, var_struct.varname, var_struct.value FROM
(
  SELECT 
    VAR1, 
    VAR2, 
    array (
         named_struct("varname","Score1","value",Score1),
         named_struct("varname","Score2","value",Score2),
         named_struct("varname","Score3","value",Score3)
    ) AS struct_array1
  FROM OrignalTable
) t1 LATERAL VIEW explode(struct_array1) t2 as var_struct;