Sql实现行列转换方便了我们存储数据和呈现数据

从MS Sql Server 2005微软就推出了pivot和unpivot实现行列转换，这极大的方便了我们存储数据和呈现数据。今天就对这两个关键字进行分析，结合实例讲解如何存储数据，如何呈现数据。
例如学生选课和成绩系统中就有一张表，该表存储了学生的课程成绩，我们无法去预料课程的多少，因此一般表会设计为下面这样：
图1
 
最后一列是课程编号，这样无论开学之后还会不会增加课程供学生选择，都没有关系。那么我们要呈现给用户看的报表一般是这样的：
图2
 
可以看到存储数据的时候采用的是列式存储，最终呈现的数据是行式显示，如何实现？下面详细分析讲解：
创建表语句


代码如下:

USE [master]
GO
/****** Object: Table [dbo].[Table_1] Script Date: 08/06/2013 13:55:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table_1](
[name] [varchar](50) NOT NULL,
[score] [real] NOT NULL,
[subject_id] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

插入测试数据


代码如下:

insert into [master].[dbo].[Table_1] ([name],[score],[subject_id]) values( '张三' , 90 , 1 );
insert into [master].[dbo].[Table_1] ([name],[score],[subject_id]) values( '张三' , 80 , 2 );
insert into [master].[dbo].[Table_1] ([name],[score],[subject_id]) values( '张三' , 70 , 3 );
insert into [master].[dbo].[Table_1] ([name],[score],[subject_id]) values( '王五' , 50 , 1 );
insert into [master].[dbo].[Table_1] ([name],[score],[subject_id]) values( '王五' , 40 , 2 );
insert into [master].[dbo].[Table_1] ([name],[score],[subject_id]) values( '李四' , 60 , 1 );

现在查询下Table_1中的数据即为图1中的结果，现在我们要得到图2的结果，那么使用下面的语句：


代码如下:

SELECT [name],[1],[2],[3]
FROM [master].[dbo].[Table_1]
pivot
(
sum(score) for subject_id in ([1],[2],[3])
) as pvt
GO

如果本身数据库表存储的就是图2那样，要变成图1的方式呈现，那就需要用unpivot，可以这样做：


代码如下:

SELECT [name],[subject_id],[score]
FROM
(
SELECT [name],[1],[2],[3]
FROM [master].[dbo].[Table_1]
pivot
(
sum(score) for subject_id in ([1],[2],[3])
) as pvt
) p
unpivot
(
score for subject_id in([1],[2],[3])
) as unpvt

当然我还是在Table_1的基础上先用pvt转为为行式存储的方式，再用unpivot进行列式呈现。

赞 (0)

相关推荐

  • Oracle实现行列转换的方法分析

    本文实例讲述了Oracle实现行列转换的方法.分享给大家供大家参考,具体如下: 1.固定列数的行列转换 如: student subject grade --------- ---------- -------- student1 语文 80 student1 数学 70 student1 英语 60 student2 语文 90 student2 数学 80 student2 英语 100 -- 转换为: 语文 数学 英语 student1 80 70 60 student2 90 80 10

  • 数据库实现行列转换(mysql示例)

    这篇文章通过sql示例代码给大家介绍了mysql数据库如何实现行列转换,下面话不多说,直接来看示例代码吧. 原表: 表名 :user ---------------------------------------- name | course | grade ---------------------------------------- zhangsan | Java | 70 ---------------------------------------- zhangsan | C++ |

  • JS控制网页动态生成任意行列数表格的方法

    本文实例讲述了JS控制网页动态生成任意行列数表格的方法.分享给大家供大家参考.具体分析如下: 这是一个非常简单使用的JS在线生成表格的代码效果 通过JS功能代码,直接输入行数和列数就可以自动生成你需要的表格 当然你也可以扩展JS代码实现生成文字的各种形式 复制代码 代码如下: <html> <head> <title>Js动态生成表格</title> <style type="text/css"> table{font-siz

  • sql 普通行列转换

    问题:假设有张学生成绩表(tb)如下: 姓名 课程 分数 张三 语文 74 张三 数学 83 张三 物理 93 李四 语文 74 李四 数学 84 李四 物理 94 想变成(得到如下结果): 姓名 语文 数学 物理 ---- ---- ---- ---- 李四 74 84 94 张三 74 83 93 ------------------- */ create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int) insert into tb va

  • C#中DataTable实现行列转换的方法

    本文实例讲述了C#中DataTable实现行列转换的方法.分享给大家供大家参考.具体实现方法如下: protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DataTable tt = GetCrossTable(CreateDT()); GridView1.DataSource = tt; GridView1.DataBind(); } } //创建DataTable protected DataTab

  • 深入SQL中PIVOT 行列转换详解

    深入SQL中PIVOT 行列转换详解

    PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合.UNPIVOT与PIVOT执行相反的操作,将表值表达式的列转换为列值. 通俗简单的说:PIVOT就是行转列,UNPIVOT就是列传行 一.PIVOT实例 1. 建表 建立一个销售情况表,其中,year字段表示年份,quarter字段表示季度,amount字段表示销售额.quarter字段分别用Q1, Q2, Q3, Q4表示一.二.三.四季度. CREATE TABLE

  • 玩转-SQL2005数据库行列转换

    玩转-SQL2005数据库行列转换

    注意:列转行的方法可能是我独创的了,呵呵,因为在网上找不到哦,全部是我自己写的,用到了系统的SysColumns (一)行转列的方法 先说说行转列的方法,这个就比较好想了,利用拼sql和case when解决即可 实现目的 1:建立测试用的数据库 复制代码 代码如下: CREATE TABLE RowTest(    [Name] [nvarchar](10) NULL,--名稱    [Course] [nvarchar](10) NULL,--課程名稱    [Record] [int] N

  • sqlserver2005 行列转换实现方法

    复制代码 代码如下: --Create Company Table Create Table Company ( ComID varchar(50) primary key, ComName nvarchar(50) not null, ComNumber varchar(50) not null, ComAddress nvarchar(200), ComTele varchar(50) ) --Create Product Table Create Table Product ( Produ

  • 浅析SQL语句行列转换的两种方法 case...when与pivot函数的应用

    /*创建数据库*/ CREATE DATABASE tmp go USE tmp go /*创建数据库测试表*/ CREATE TABLE [Scores]     (       [ID] INT IDENTITY(1, 1)                PRIMARY KEY ,       [Student] VARCHAR(20) ,       [Subject] VARCHAR(30) ,       [Score] FLOAT     ) go TRUNCATE TABLE Sc

  • 一个简单的SQL 行列转换语句

    一个简单的SQL 行列转换 Author: eaglet 在数据库开发中经常会遇到行列转换的问题,比如下面的问题,部门,员工和员工类型三张表,我们要统计类似这样的列表 部门编号 部门名称 合计 正式员工 临时员工 辞退员工 1 A 30 20 10 1 这种问题咋一看摸不着头绪,不过把思路理顺后再看,本质就是一个行列转换的问题.下面我结合这个简单的例子来实现行列转换. 下面3张表 复制代码 代码如下: if exists ( select * from sysobjects where id =

随机推荐