SQL server 2008数据库查询中的数据透视与数据逆透视

SqlServer⽣成数据透视表（⾏列转换等经典SQL语句）数据透视表是分析数据的⼀种⽅法，在Excel中就包含了强⼤的数据透视功能。

数据透视是什么样的呢？给个例⼦可能更容易理解。

假设有⼀张数据表：销售⼈员书籍销量----------------------------------------⼩王 Excel教材 10⼩李 Excel教材 15⼩王 Word教材 8⼩李 Excel教材 7⼩王 Excel教材 9⼩李 Excel教材 2⼩王 Word教材 3⼩李 Excel教材 5⼀种数据透视的⽅法是统计每个销售⼈员对每种书籍的销量 ,结果如下----------------------------------------------------------------Excel教材Word教材总计---------------------------------------------- -----------------⼩王 29 0 29⼩李 19 11 30各位看明⽩了吗？这是最简单的⼀种数据透视了，如果有必要也可以有多级分组。

好了，那在Sql Server中如何视现数据透视的功能呢？我是Sql Server的初学者，看了⽹上的⼀些例⼦，结合⾃⼰的理解写了下⾯这些Sql语句.⽣成基础数据的代码Create table s(View Code[name] nvarchar(50),book nvarchar(50),saledNumber int)insert into s ([name],book,saledNumber) values('⼩王','Excel教材',10);insert into s ([name],book,saledNumber)values('⼩李','Excel教材',15);insert into s ([name],book,saledNumber)values('⼩王','Word教材',8);insert into s ([name],book,saledNumber)values('⼩李','Excel教材',7);insert into s ([name],book,saledNumber)values('⼩王','Excel教材',9);insert into s ([name],book,saledNumber)values('⼩李','Excel教材',2);insert into s ([name],book,saledNumber)values('⼩王','Word教材',3);insert into s ([name],book,saledNumber)values('⼩李','Excel教材',5);⽣成数据透视表View Codeset @sql = 'SELECT [name], 'select @sql = @sql + 'sum(case book when '+quotename(book,'''')+' then saledNumber else 0 end) as ' + quotename(book)+','from s group by bookselect @sql = left(@sql,len(@sql)-1)select @sql = @sql + ', sum(saledNumber) as [sum] from s group by [name]'select @sqlexec(@sql)上⾯的查询语句⾸先是拼接了⼀条"Sql语句",它的最终结果为:SELECT[name], sum(case book when'Excel教材'then saledNumber else0end) as[Excel教材],sum(case book when'Word教材'then saledNumber else0end) as[Word教材], sum(saledNumber) as[sum]from s group by[name]当然，如果表中的数据不同，那么这⽣成的Sql语句也是不同的。

sqlserver2008查询语句SQL Server 2008是一种关系型数据库管理系统，它支持使用SQL 语言进行数据查询和操作。

在本文中，我们将列举一些常用的SQL Server 2008查询语句，以帮助读者更好地了解和使用这个数据库管理系统。

1. 查询表中的所有数据SELECT * FROM table_name;这个查询语句可以用来查询指定表中的所有数据。

其中，table_name是要查询的表的名称。

2. 查询表中的部分数据SELECT column1, column2, ... FROM table_name WHERE condition;这个查询语句可以用来查询指定表中符合条件的部分数据。

其中，column1, column2, ...是要查询的列的名称，condition是查询条件。

3. 查询表中的唯一数据SELECT DISTINCT column1, column2, ... FROM table_name;这个查询语句可以用来查询指定表中唯一的数据。

其中，column1, column2, ...是要查询的列的名称。

4. 对查询结果进行排序SELECT column1, column2, ... FROM table_name ORDER BY column_name ASC|DESC;这个查询语句可以用来对查询结果进行排序。

其中，column1, column2, ...是要查询的列的名称，column_name是要排序的列的名称，ASC表示升序排列，DESC表示降序排列。

5. 对查询结果进行分组SELECT column1, column2, ... FROM table_name GROUP BY column_name;这个查询语句可以用来对查询结果进行分组。

其中，column1, column2, ...是要查询的列的名称，column_name是要分组的列的名称。

利用数据透析表进行数据透视与逆向透视数据透析表（Pivot Table）是一种用于对大量数据进行分析和汇总的工具。

它可以通过透视操作轻松地对数据进行排列、计算和摘要，并以易于理解的方式呈现结果。

在本文中，我们将介绍数据透析表的使用方法以及如何进行逆向透视分析。

数据透析表的使用方法非常简单，首先我们需要拥有一组具有特定格式的源数据。

这些源数据可以来自于数据库、电子表格或其他数据源。

接下来，我们需要打开数据透析表功能并选择要分析的数据范围。

然后，我们可以将数据按照需要的方式进行分组、筛选和排序，进而进行数据透析。

最后，我们可以在透析表中进行各种计算和汇总操作，并生成相应的报表或图表。

透视分析的一个常见用途是对数据进行分类和汇总。

通过将数据按照某一字段进行分组，我们可以了解到不同类别的数据在整体数据中的分布和比例。

比如，在销售数据中，我们可以通过透析表将销售额按照产品、地区或时间进行分类，并计算出各个分类的销售额占比。

这有助于我们识别出销售额较高的产品、热门地区和销售旺季，从而做出更准确的决策。

除了分类和汇总之外，透视分析还可以进行计算和摘要。

在数据透析表中，我们可以添加字段或计算项来进行各种计算操作，如求和、平均值、最大值、最小值等。

这些计算结果可以帮助我们更深入地了解数据的特征和趋势。

例如，在财务数据中，我们可以通过逆向透析分析计算各个部门或项目的利润率，以及利润的增长或减少趋势，以此来评估业务的盈利状况。

逆向透视（Reverse Pivot）是数据透析表的一个扩展功能，它可以帮助我们更方便地进行逆向分析。

逆向分析是指我们可以通过逆向透析表来了解某个计算结果是如何得出的。

简单来说，逆向透析表可以将计算结果“还原”为原始数据及其操作步骤。

这对于数据分析师来说非常有价值，因为它可以帮助我们理解背后的逻辑和算法。

逆向透析的操作方法与数据透析类似。

我们需要选择一个计算结果，并右键点击该结果，然后选择“逆向透析”。

sql透视与逆透视今天抽一点时间来看看透视和逆透视语句，简单的说就是行列转换。

假设一个销售表中存放着产品号，产品折扣，产品价格三个列，每一种产品号可能有多种折扣，每一种折扣只对应一个产品价格。

下面贴出建表语句和插入数据语句。

1create table SalesOrderDetail(2ProductID int /*unique多谢wuu00的提醒*/,3UnitPriceDiscount float,4ProductPrice float5)6insert into SalesOrderDetail values7(711,.00,12),8(711,.00,13),9(711,.02,17),10(711,.02,16),11(711,.05,19),12(711,.05,20),13(711,.10,21),14(711,.10,22),15(711,.15,23),16(711,.15,24),17(747,.00,41),18(747,.00,42),19(747,.02,45),20(747,.02,46),21(776,.20,50),22(776,.20,49),23(776,.35,52),24(776,.35,53)首先来看一条查询语句1select ProductID,UnitPriceDiscount,SUM(ProductPrice) as SumPrice 2from SalesOrderDetail3group by ProductID,UnitPriceDiscount4order by ProductID,UnitPriceDiscount这条语句查询每一种产品针对每一种折扣的价钱总和，查询结果如下图1图1从图中我们可以看出771号产品有4种折扣，747号产品有2种折扣，776号产品有2种折扣。

现在如果我们想知道每一种产品折扣，每一种产品的销售总价是多少，如下图2图2如图对于折扣0，产品711的总价是25，对以折扣0.02，产品711的总价是33等等不再列举。

数据透视方法"数据透视方法" 通常指的是一种数据分析技术，主要用于在大量数据中发现模式、趋势和关联性。

以下是数据透视方法的一般步骤和一些相关的概念：1.数据透视表（Pivot Table）：数据透视表是数据透视方法的核心工具之一。

它允许用户对大量数据进行动态排列和汇总，以便更容易地识别模式和关系。

用户可以通过拖放字段来动态调整数据的布局，以满足分析需求。

2.字段和值：在数据透视表中，数据字段是指数据的属性或特征，而字段的值则是具体的数据。

在数据透视过程中，用户通常会选择特定的字段作为行标签、列标签和值，以便对数据进行组织和分析。

3.行和列：数据透视表允许用户将数据按照行和列进行组织。

行标签和列标签是数据透视表中的两个关键概念，它们确定了如何对数据进行分类和分组。

4.值区域：值区域是数据透视表中用于聚合和汇总数据的区域。

用户可以选择在值区域中放置数值字段，以便对其进行统计，如求和、平均值等。

5.筛选和过滤：数据透视表通常提供了筛选和过滤数据的功能，以便用户可以集中关注感兴趣的数据子集。

这有助于更精确地分析特定条件下的数据。

6.图表和图形：一些数据透视工具还允许用户通过图表和图形直观地呈现数据。

这有助于更好地理解数据的分布和关系。

7.数据透视的应用领域：数据透视方法广泛应用于商业分析、市场调研、财务分析等领域。

它能够帮助用户从大量数据中提取关键信息，做出更明智的决策。

常见的数据透视工具包括Microsoft Excel 中的数据透视表功能、数据库管理系统中的SQL 查询、以及专业的商业智能工具如Tableau、Power BI等。

这些工具提供了用户友好的界面，使得数据透视方法更加容易实施和理解。

1.PIVOT 语法。

SELECT <非透视的列>,[第一个透视的列] AS <列名称>,[第二个透视的列] AS <列名称>,...[最后一个透视的列] AS <列名称>,FROM(<生成数据的SELECT 查询>)AS <源查询的别名>PIVOT(<聚合函数>(<要聚合的列>)FOR[<包含要成为列标题的值的列>]IN ( [第一个透视的列], [第二个透视的列],... [最后一个透视的列])) AS <透视表的别名><可选的ORDER BY 子句>;2.PIVOT 执行过程：（1）in后面的行值称为非透视列；（2）查询时先对非透视列的非聚合列进行分组；用一般查询语句表示如下：Select 非透视列的非聚合列(包含要成为列标题的值的列), 聚合函数(要聚合的列)From 源表Group by 非透视列的非聚合列（3）将要成为列标题的值转化成透视列，其值为（2）的查询结果集中对应的聚合函数(要聚合的列)值；（4）由于执行步骤（3）后，原分组列中少了(包含要成为列标题的值的列)，因此以剩下的列再分组，这可能会导致结果集的某条记录的透视列有多个值。

3．例题：1）源数据：CREATE TABLE[dbo].[CJB]([学号][char](6)NOT NULL,[课程号][char](3)NOT NULL,[成绩][int]NULL)ON[PRIMARY]INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101101',N'101', 80) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101101',N'102', 78) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101101',N'206', 76) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101103',N'101', 62) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101103',N'102', 70) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101103',N'206', 81) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101104',N'101', 90) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101104',N'102', 84) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101104',N'206', 65) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101102',N'102', 78) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101102',N'206', 78) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101106',N'101', 65) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101106',N'102', 71) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101106',N'206', 80) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101107',N'101', 78) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101107',N'102', 80) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101107',N'206', 68) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101108',N'101', 85) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101108',N'102', 64) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101108',N'206', 87) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101109',N'101', 66) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101109',N'102', 83) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101109',N'206', 70) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101110',N'101', 95) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101110',N'102', 90) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101110',N'206', 89) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101111',N'101', 91) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101111',N'102', 70) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101111',N'206', 76) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101113',N'101', 63) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101113',N'102', 79) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101113',N'206', 60)INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101201',N'101', 80) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101202',N'101', 65) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101203',N'101', 87) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101204',N'101', 91) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101210',N'101', 76) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101216',N'101', 81) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101218',N'101', 70) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101220',N'101', 82) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101221',N'101', 76) INSERT[dbo].[CJB]([学号],[课程号],[成绩])VALUES (N'101241',N'101', 90)以上（共42条记录）2）透视查询语句：select'选课门数',成绩,[101101],[101102],[101103]from cjb as selpivot(count(课程号)for学号in([101101],[101102],[101103]))as pvt其查询结果为（共23条记录）：表13）透视查询语句执行过程首先对非透视列的非聚合列进行分组；用一般的查询语句代替的语法为：select学号,成绩,count(课程号)as'选课门数'from cjbgroup by学号,成绩上述查询中学号与成绩为非透视列的非聚合列，课程号为聚合列。

数据透析表的数据透析视图与数据透析表数据透视器逆透析技巧数据透析是一种重要的数据分析方法，通过对数据进行透析，可以发现其中潜在的规律和趋势。

在进行数据透析时，我们通常使用数据透析表和数据透析视图，以及数据透析表数据透视器逆透析技巧来辅助分析。

数据透析视图是指对数据透析表的一种视图展示方式，通过对数据透析表进行整理和聚合，可以形成一种更直观、更易读的数据展示形式。

数据透析视图可以帮助分析人员更好地理解和把握数据的特点，加深对数据的认识。

在创建数据透析视图时，首先需要明确透析的维度和度量。

维度是指我们对数据进行透析的属性，比如时间、地点、产品等。

度量是指对维度进行计算和聚合的指标，比如销售额、利润率等。

根据不同的需求，我们可以选择合适的维度和度量来创建数据透析视图。

创建数据透析视图的步骤包括选择透析的维度和度量、进行数据的筛选和过滤、进行数据的分组和聚合，最后根据需求进行数据的展示和分析。

数据透析视图可以采用图表、表格等形式来展示数据，使数据更易于理解和分析。

数据透析表数据透视器逆透析技巧是一种针对数据透析表的逆向分析技巧。

逆透析是指通过已有的透析结果，推导出透析表中的原始数据。

逆透析技巧可以帮助分析人员从结果中还原出原始数据，找到导致结果的原因和影响。

在进行数据透析表数据透视器逆透析时，我们需要首先了解透析表的结构和透析的方法。

透析表的结构包括维度、度量和透析过程。

维度和度量已经在前文中提到，透析过程是指对数据进行透析的步骤和方法。

逆透析的关键是找到透析过程中的逆运算方法。

具体来说，我们可以通过反向计算和分析来还原出透析表中的原始数据。

这可以通过多种逆运算方法来实现，比如逆向加减法、逆向计算公式等。

通过逆透析技巧，我们可以进一步理解和分析数据透析的结果。

除了逆透析技巧，我们还可以使用其他的数据分析方法来辅助透析表的分析。

比如数据挖掘、机器学习等方法可以帮助我们从透析表中发现潜在的规律和趋势。

这些方法可以通过对透析表进行统计分析、模式识别等来实现。

一、介绍SQL Server透视表语句SQL Server是一款常用的关系型数据库管理系统，透视表语句是SQL Server中非常重要且常用的功能之一。

透视表语句用于将行数据转换为列数据，以便更直观地比较和分析不同维度的数据。

在数据分析和报表生成中，透视表语句能够方便地实现数据的汇总、统计和展示，使得数据分析工作更加高效和直观。

二、透视表语句的基本语法在SQL Server中，透视表语句的基本语法如下所示：```SELECT Category, [1] AS January, [2] AS February, [3] AS March FROM(SELECT Category, Amount, MONTH(OrderDate) AS Month FROM Sales) AS SourceTablePIVOT(SUM(Amount)FOR Month IN ([1], [2], [3])) AS PivotTable;```在上面的例子中，我们首先从Sales表中选择Category、Amount和OrderDate列的数据，然后使用PIVOT关键字将月份数据进行透视，最后将转换后的数据列别名为January、February和March。

三、透视表语句的应用场景透视表语句在实际的数据分析和报表生成中有着广泛的应用场景。

在销售数据分析中，我们可以使用透视表语句将不同产品类别在不同月份的销售额进行汇总和展示，以便直观地了解产品销售情况。

在财务报表生成中，透视表语句能够方便地生成按月份或者按季度的财务报表，帮助财务人员快速了解企业的财务状况。

四、透视表语句的优势透视表语句相比传统的分组聚合函数，具有以下几点优势：1. 更直观的展示形式：透视表语句将行数据转换为列数据，更符合人类对数据的理解习惯，能够更直观地展示数据分布和变化趋势。

2. 灵活性高：透视表语句可以根据需要动态地转换数据的列，适用于不同维度和不同粒度的数据分析和展示。

在SQLSERVER中实现数据透视表的代码在SQL SERVER 中实现数据透视表的代码KIM笔者乃计算机程序小白，在网上搜索良久，终于找到能够看懂并进行运用的可以在SQL SERVER 中实现数据透视表的代码（使用的版本为SQL SERVER2008），分享如下：表样如下：库名：[学习]表名：[2018]表内字段如下：代码如下：declare @sql varchar(8000)set @sql='select [总账科目],[总账科目：长文本] ,'select @sql=@sql+'sum(case when[功能范围]+[功能范围：文本]='''+组合+''' then 凭证货币价值else 0 end)['+组合+'求和列1],'from (select distinct top 100 percent [功能范围]+[功能范围：文本] AS 组合from [学习].[dbo].[2018] where len([功能范围：文本])>1 order by 组合)bset @sql =left(@sql,len(@sql)-1)+'from [学习].[dbo].[2018]where len([功能范围：文本])>1 group by [总账科目],[总账科目：长文本]'exec(@sql)代码使用说明；代码中标颜色的部分需要根据实际需要修改，黑色的部分不用修改1)上述代码中橙色的部分为数据透视引用表的表名2)上述代码中红色的部分为列示在数据透视表的纵列的字段名称，可以多个，多个用,连接3)上述代码中紫色的部分为列示数据透视表的横列的字段名称，可以多个，多个用+连接4)上述代码中深绿色为想要显示的求和项的名称，是自己新取的5)上述代码中绿色的部分为求和列的字段名称，可以多个，多个举例如下：declare @sql varchar(8000)set @sql='select [总账科目],[总账科目：长文本] ,'select @sql=@sql+'sum(case when[功能范围]+[功能范围：文本]='''+组合+''' then 凭证货币价值else 0 end)['+组合+'求和列1],' +'sum(case when [功能范围]+[功能范围：文本]='''+组合+''' then 公司代码货币价值else 0 end) ['+组合+'求和列2],'from (select distinct top 100 percent [功能范围]+[功能范围：文本] AS 组合from [学习].[dbo].[2018] where len([功能范围：文本])>1 order by 组合)bset @sql =left(@sql,len(@sql)-1)+'from [学习].[dbo].[2018]where len([功能范围：文本])>1 group by [总账科目],[总账科目：长文本]'exec(@sql)6)上述代码中蓝色的部分是非必要项目，如果是需要对数据库中的部分数据进行数据透视，可以通过增加where 来实现，注意如果第二个where 后需要用到单引号的话，则需要将'（单引号）变成''(两个单引号)，举例：declare @sql varchar(8000)set @sql='select 客户,'select @sql=@sql+'sum(case when过帐期间='''+过帐期间+''' then 凭证货币价值else 0 end)['+过帐期间+'],'from (select distinct top 100 percent 过帐期间from [学习].[dbo].[2018] where [总账科目：长文本] ='应收账款_集团内_国内' order by 过帐期间)aset @sql =left(@sql,len(@sql)-1)+'from [学习].[dbo].[2018] where [总账科目：长文本] =''应收账款_集团内_国内''group by 客户' exec(@sql)。