在SQL中将数据从宽格式转换为长格式 [英] Convert data from wide format to long format in SQL

问题描述

我有一些格式的数据：

  VAR1 VAR2得分1得分2得分3 
 AB 1 2 3 
  

我需要将它转换为格式

 VAR1 VAR2 VarName价值
 AB得分1 1 
 AB得分2 2 
 AB得分3 3 
  

如何在SQL中执行此操作？

解决方案

提供您的分数列是固定的，你不需要聚合，你可以使用多个 SELECT 和 UNION ALL 语句来生成数据的形状请求。例如

  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小提琴： http://sqlfiddle.com/#!6/f54b2/4/0

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?

解决方案

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

这篇关于在SQL中将数据从宽格式转换为长格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！