纵表SQL Server数据库横表转换到纵表的技术实现

横表转换到纵表的定义

在SQL Server数据库中，横表与纵表是两种数据呈现方式。横表将不同的属性值作为列名，而将属性值作为行数据，这种方式更为适合于数据的查看，例如：

  CREATE TABLE T1 
  (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(10) NOT NULL,
    Age INT NOT NULL,
    Gender CHAR(1) NOT NULL,
    Course1 INT NOT NULL,
    Course2 INT NOT NULL,
    Course3 INT NOT NULL
  )
  INSERT INTO T1 VALUES('Tom', 20, 'M', 80, 90, 70); 
  INSERT INTO T1 VALUES('Jerry', 19, 'M', 90, 70, 90); 
  INSERT INTO T1 VALUES('Lucy', 18, 'F', 60, 80, 70); 
  INSERT INTO T1 VALUES('Lily', 22, 'F', 90, 70, 80); 

上述代码中，表T1将各个课程的成绩作为列名，而对应的成绩则作为行数据。但是，对于数据的增删改操作，横表的构造比较困难，因此需要将横表转换为纵表的数据结构。

纵表的数据结构

纵向的表结构更加适用于数据库的增删改操作，常用的纵向表结构定义如下：

  CREATE TABLE T2 
  (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(10) NOT NULL,
    Age INT NOT NULL,
    Gender CHAR(1) NOT NULL,
    CourseName VARCHAR(10) NOT NULL,
    Score INT NOT NULL
  )
  INSERT INTO T2 VALUES('Tom', 20, 'M', 'Course1', 80); 
  INSERT INTO T2 VALUES('Tom', 20, 'M', 'Course2', 90); 
  INSERT INTO T2 VALUES('Tom', 20, 'M', 'Course3', 70); 
  INSERT INTO T2 VALUES('Jerry', 19, 'M', 'Course1', 90); 
  INSERT INTO T2 VALUES('Jerry', 19, 'M', 'Course2', 70); 
  INSERT INTO T2 VALUES('Jerry', 19, 'M', 'Course3', 90); 
  INSERT INTO T2 VALUES('Lucy', 18, 'F', 'Course1', 60); 
  INSERT INTO T2 VALUES('Lucy', 18, 'F', 'Course2', 80); 
  INSERT INTO T2 VALUES('Lucy', 18, 'F', 'Course3', 70); 
  INSERT INTO T2 VALUES('Lily', 22, 'F', 'Course1', 90); 
  INSERT INTO T2 VALUES('Lily', 22, 'F', 'Course2', 70); 
  INSERT INTO T2 VALUES('Lily', 22, 'F', 'Course3', 80); 

纵表的数据结构中，每个课程的名称与成绩都表示为一条记录。

横表转换到纵表的技术实现

使用UNPIVOT函数实现

SQL Server的UNPIVOT函数可以将横向的表结构转换为纵向的表结构：

  SELECT ID, Name, Age, Gender, CourseName, Score
  FROM
  (
    SELECT ID, Name, Age, Gender, 
    CAST(Course1 AS VARCHAR(10)) AS Course1, 
    CAST(Course2 AS VARCHAR(10)) AS Course2, 
    CAST(Course3 AS VARCHAR(10)) AS Course3
    FROM T1
  ) P
  UNPIVOT
  (
    Score FOR CourseName IN (Course1, Course2, Course3)
  ) AS U

以上代码中，通过UNPIVOT函数将表T1中的‘Course1’，‘Course2’，‘Course3’三列中的值作为Score列的记录，并增加CourseName作为课程名列。UNPIVOT函数用法：

在SELECT语句中，将横向表结构转换为纵向表结构，例如将’Course1’中的值作为Score列的记录，同时增加课程名称为CourseName列

课程名称通过FOR IN ()关键字指定

UNPIVOT函数可以使用多个关键字指定多列值的转换

使用CROSS APPLY实现

另一种方式是使用CROSS APPLY：

  SELECT ID, Name, Age, Gender, CourseName, Score
  FROM T1
  CROSS APPLY (
    VALUES
    ('Course1', Course1),
    ('Course2', Course2),
    ('Course3', Course3)
  ) AS U(CourseName, Score)

CROSS APPLY函数是SQL Server SQL 2005版本中的扩展功能，可以用于在不同的结果集之间执行查询，通过VALUES指令来指定需要转换的列。

横表转换到纵表的局限性

由横表转换为纵表可以提高对数据的管理和导入，但在某些情况下也会存在一些限制：

当表中的数据规模过大时，转换会造成系统性能问题。如果转换的记录数量比原有的要多，查询性能也会受到影响

将横表转换为纵表后，需要进行多次查询才能获得全面的数据。如果需要在单个查询中检索数据，可能需要维护一个与横表极其相似的视图或临时表

总结

此篇文章主要介绍了在SQL Server数据库中如何实现横表转换到纵表，方法包括UNPIVOT函数和CROSS APPLY。横表数据结构的缺完更高的管理难度和对性能的影响，而纵表模式则更适合于对数据库的增删改操作。