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?

Please give more information about the structure of your data. Are the snippets you've posted column names or values? Where does `Value` come from? — Chris Pickford, Apr 22 '15 at 09:08

score 6 · Accepted Answer · answered Apr 22 '15 at 09:39

6

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

answered Apr 22 '15 at 09:39

Chris Pickford

8,283
5
44
71

ERROR: column "varname" does not exist in table – Abhinav Ravi Jun 14 '19 at 08:54
Is there another way of doing this if there where more Score variables? – Max M May 11 '20 at 16:41

score 1 · Answer 2 · answered Dec 30 '16 at 12:25

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;

Convert data from wide format to long format in SQL

2 Answers2

Linked