`
weitao1026
  • 浏览: 1048609 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

SQL行转列汇总

 
阅读更多

PIVOT 用于将列值旋转为列名（即行转列），在 SQL Server 2000可以用聚合函数配合CASE语句实现

PIVOT 的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P

注意：PIVOT、UNPIVOT是SQL Server 2005 的语法，使用需修改数据库兼容级别(在数据库属性->选项->兼容级别改为   90 )

SQL2008 中可以直接使用

完整语法：

table_source

PIVOT(

聚合函数（value_column）

FOR pivot_column

IN(<column_list>)

)
View Code

UNPIVOT 用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现

完整语法：

table_source

UNPIVOT(

value_column

FOR pivot_column

IN(<column_list>)

)
View Code

 

典型实例

一、行转列

1、建立表格

复制代码
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb

go

CREATE TABLE tb(姓名 VARCHAR(10),课程 VARCHAR(10),分数 INT)

insert into tb VALUES ('张三','语文',74)
insert into tb VALUES ('张三','数学',83)
insert into tb VALUES ('张三','物理',93)
insert into tb VALUES ('李四','语文',74)
insert into tb VALUES ('李四','数学',84)
insert into tb VALUES ('李四','物理',94)

go

SELECT * FROM tb

go
复制代码

姓名       课程       分数

---------- ---------- -----------

张三       语文        74

张三       数学        83

张三       物理        93

李四       语文        74

李四       数学        84

李四       物理        94

 

2、使用SQL Server 2000静态SQL

复制代码
SELECT 姓名,

 max(CASE 课程 WHEN'语文' THEN 分数 ELSE 0 END) 语文,

 max(CASE 课程 WHEN'数学' THEN 分数 ELSE 0 END) 数学,

 max(CASE 课程 WHEN'物理' THEN 分数 ELSE 0 END) 物理

FROM tb

GROUP BY 姓名
复制代码

3、使用SQL Server 2000动态SQL

复制代码
--SQL SERVER 2000动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)

--变量按sql语言顺序赋值

declare@sqlvarchar(500)

set@sql='select姓名'

select@sql=@sql+',max(case课程when '''+课程+''' then分数else 0 end)['+课程+']'

from(selectdistinct课程fromtb)a--同from tb group by课程，默认按课程名排序

set@sql=@sql+' from tb group by姓名'

exec(@sql)

 

--使用isnull(),变量先确定动态部分

declare@sqlvarchar(8000)

select@sql=isnull(@sql+',','')+' max(case课程when '''+课程+''' then分数else 0 end) ['+课程+']'

from(selectdistinct课程fromtb)asa      

set@sql='select姓名,'+@sql+' from tb group by姓名'

exec(@sql)
复制代码

4、使用SQL Server 2005静态SQL

SELECT * FROM tb pivot( MAX(分数) FOR 课程 IN (语文,数学,物理))a

姓名       语文        数学        物理

---------- ----------- ----------- -----------

李四        74          84          94

张三        74          83          93

5、使用SQL Server 2005动态SQL

复制代码
--使用stuff()

DECLARE @sql VARCHAR(8000)

SET @sql=''  --初始化变量 @sql

SELECT @sql= @sql+',' + 课程 FROM tb GROUP BY 课程 --变量多值赋值

SET @sql= STUFF(@sql,1,1,'')--去掉首个','

SET @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+'))a'

PRINT @sql

exec(@sql)

--或使用isnull()

DECLARE @sql VARCHAR(8000)

--获得课程集合

SELECT @sql= ISNULL(@sql+',','')+课程 FROM tb
GROUP BY 课程           

SET @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+'))a'

exec(@sql)
复制代码

二、行转列结果加上总分、平均分

1、使用SQL Server 2000静态SQL

--SQL SERVER 2000静态SQL

select姓名,

max(case课程when'语文'then分数else0end)语文,

max(case课程when'数学'then分数else0end)数学,

max(case课程when'物理'then分数else0end)物理,

sum(分数)总分,

cast(avg(分数*1.0)asdecimal(18,2))平均分

fromtb

groupby姓名
View Code

姓名       语文        数学        物理        总分        平均分

---------- ----------- ----------- ----------- -----------

李四        74          84          94          252         84.00

张三        74          83          93          250         83.33

 

2、使用SQL Server 2000动态SQL

--SQL SERVER 2000动态SQL

declare@sqlvarchar(500)

set@sql='select姓名'

select@sql=@sql+',max(case课程when '''+课程+''' then分数else 0 end)['+课程+']'

from(selectdistinct课程fromtb)a

set@sql=@sql+',sum(分数)总分,cast(avg(分数*1.0) as decimal(18,2))      平均分from tb group by姓名'

exec(@sql)
View Code

 

3、使用SQL Server 2005静态SQL

复制代码
SELECT  m.* ,
        n.总分 ,
        n.平均分
FROM    ( SELECT    *
          FROM      tb PIVOT( MAX(分数) FOR 课程 IN ( 语文, 数学, 物理 ) ) a
        ) m ,
        ( SELECT    姓名 ,
                    SUM(分数) 总分 ,
                    CAST(AVG(分数 * 1.0) AS DECIMAL(18, 2)) 平均分
          FROM      tb
          GROUP BY  姓名
        ) n
WHERE   m.姓名 = n.姓名
复制代码

4、使用SQL Server 2005动态SQL

--使用stuff()
DECLARE @sql VARCHAR(8000)

SET @sql = ''
  --初始化变量@sql

SELECT  @sql = @sql + ',' + 课程
FROM    tb
GROUP BY 课程
--变量多值赋值

--同select @sql = @sql + ','+课程 from (select distinct 课程 from tb)a

SET @sql = STUFF(@sql, 1, 1, '')
--去掉首个','

SET @sql = ' select m.* , n.总分,n.平均分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql
    + ')) b) m ,

(select 姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2)) 平均分 from tb group by 姓名) n

where m.姓名= n.姓名'

EXEC(@sql)
 

--或使用isnull()

DECLARE @sql VARCHAR(8000)

SELECT  @sql = ISNULL(@sql + ',', '') + 课程
FROM    tb
GROUP BY 课程

SET @sql = 'select m.* , n.总分,n.平均分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql
    + ')) b) m ,

(select 姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2)) 平均分 from tb group by 姓名) n

where m.姓名= n.姓名'

EXEC(@sql)
View Code

 

二、列转行

1、建立表格

复制代码
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
go

CREATE TABLE tb(姓名 VARCHAR(10),语文 INT,数学 INT,物理 INT)

INSERT INTO tb VALUES('张三',74,83,93)

INSERT INTO tb VALUES('李四',74,84,94)

go

SELECT * FROM tb
复制代码

姓名       语文        数学        物理

---------- ----------- ----------- -----------

张三       74          83          93

李四        74          84          94

2、使用SQL Server 2000静态SQL

--SQL SERVER 2000静态SQL。

select*from

(

 select姓名,课程='语文',分数=语文fromtb

 unionall

 select姓名,课程='数学',分数=数学fromtb

 unionall

 select姓名,课程='物理',分数=物理fromtb

) t

orderby姓名,case课程when'语文'then1when'数学'then2when'物理'then3end
View Code

姓名       课程 分数

---------- ---- -----------

李四       语文 74

李四       数学 84

李四       物理 94

张三       语文 74

张三       数学 83

张三       物理 93

  

2、使用SQL Server 2000动态SQL

--SQL SERVER 2000动态SQL。

--调用系统表动态生态。

declare@sqlvarchar(8000)

select@sql=isnull(@sql+' union all ','')+' select姓名, [课程]='

+quotename(Name,'''')+' , [分数] = '+quotename(Name)+' from tb'

fromsyscolumns

whereName!='姓名'andID=object_id('tb')--表名tb，不包含列名为姓名的其他列

orderbycolid

exec(@sql+' order by姓名')

go
View Code

 

3、使用SQL Server 2005静态SQL

复制代码
--SQL SERVER 2005动态SQL

SELECT  姓名 ,
        课程 ,
        分数
FROM    tb UNPIVOT ( 分数 FOR 课程 IN ( [语文], [数学], [物理] ) ) t
复制代码

4、使用SQL Server 2005动态SQL

复制代码
--SQL SERVER 2005动态SQL

DECLARE @sql NVARCHAR(4000)

SELECT  @sql = ISNULL(@sql + ',', '') + QUOTENAME(name)
FROM    syscolumns
WHERE   id = OBJECT_ID('tb')
        AND name NOT IN ( '姓名' )
ORDER BY colid

SET @sql = 'select 姓名,[课程],[分数] from tb unpivot ([分数] for [课程] in(' + @sql
    + '))b'

EXEC(@sql)
复制代码
分享到：
| SQL索引一步到位
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    sql列转行以及行转列的通用存储过程

    ### 标题解析：SQL列转行及行转列的通用存储过程 该标题表明文章主要介绍在SQL中如何通过一个通用的存储过程实现列数据转化为行数据（列转行），或者反过来将行数据转化为列数据（行转列）。这两种转换方式在处理多...

    sql行转列解决方案.

    标题与描述均提到“SQL行转列解决方案”，这主要指的是在SQL中将数据表中的行数据转换为列数据的一种操作技巧，通常应用于需要对多行数据进行汇总展示或数据分析的场景。这种转换对于数据报告、统计分析等业务需求至...

    SQL行转列显示

    这种转换技术被称为“行转列”或“透视”（Pivot），而在SQL语言中实现这一功能，最常用的方法便是利用PIVOT函数。本文将深入探讨如何使用SQL中的PIVOT函数将表格数据由行格式转换为列格式，并通过具体案例进行详细...

    sql动态行转列 存储过程

    在SQL数据库操作中，"行转列"是一种常见的数据转换需求，它将表格中的多行数据转换为单列显示，通常用于数据汇总和分析。在本案例中，我们主要探讨如何使用SQL语句，尤其是存储过程，来实现动态的行转列功能。这在...

    Mysql 行转列，列转行 SQL语句和示例表结构SQL

    MySQL 提供了两种转换数据布局的方法：行转列（Pivot）和列转行（Unpivot），这在处理复杂的数据汇总和展示时非常有用。本文将深入探讨这两种转换方法，并提供具体的 SQL 语句示例，以及创建示例表结构的 SQL 代码。...

    SQL行转列范例教程

    ### SQL行转列知识点详解 #### 一、背景与需求 在数据分析领域，经常会遇到需要将数据表中的行转换为列的情况，这种操作通常被称为“行转列”或“纵转横”。例如，在处理销售数据时，可能需要将不同产品的销售额从...

    sql server 中行转列

    在 SQL Server 中，“行转列”（Pivot）是一种常用的数据处理方式，它能够将表格中的行数据转换为列数据，从而使得数据更加易于理解和分析。这种方式特别适用于将汇总数据进行重新组织的情况。 #### 二、描述：代码...

    sqlserver行变列

    ### SQL Server 行转列操作详解 在数据库管理和数据分析领域，有时我们需要将表格中的行转换成列，这种操作称为“行转列”。行转列在实际应用中非常常见，尤其是在处理具有多维度的数据集时。例如，从一个记录学生...

    行转列、列名转行

    在SQL Server中，"行转列"和"列名转行"是数据处理中的常见操作，主要用于优化数据的展示和分析。这两种操作可以利用SQL Server的特定功能来实现，如PIVOT和UNPIVOT操作，或者通过CASE语句和自连接等方法。下面将详细...

    Oracle行转列之pivot

    在Oracle数据库中，行转列（也称为数据透视）和列转行（unpivot）是SQL查询中用于数据转换的高级功能。从Oracle 11g版本开始，引入了PIVOT和UNPIVOT关键字，以支持显式的查询转换，即从行数据转换为列数据，或从列...

    Sql_Servcer行转列及列转行说明.docx

    行转列，即把数据库中的一行数据转换为多列显示，反之列转行则是将多列数据合并成一行。这里主要介绍的是使用`PIVOT`和`UNPIVOT`操作来实现这一目标。 `PIVOT`是SQL Server提供的一种关系运算符，它允许我们将一个...

    SQL 通过行动态生成列

    在SQL查询中，将行数据转换为列，也称为行转列或行列互换，是数据分析和报表制作中常见的需求。这种操作对于呈现表格数据，尤其是处理统计汇总时非常有用。"SQL 通过行动态生成列"这个主题，就是探讨如何在SQL中实现...

    mysql-行转列、列转行

    标题“mysql-行转列、列转行”涉及到的是MySQL中的两种主要转换技巧： 1. **行转列（Pivot）**： 行转列通常用于将多行数据转换为单行的多个列。在MySQL中，没有内置的PIVOT函数，但可以通过使用`CASE`语句配合`...

    游标分类汇总行转列oracleplsql

    本示例展示了如何使用Oracle PL/SQL中的游标来进行数据的分组统计，并通过程序逻辑实现了一种类似“行转列”的效果。这对于初学者来说是非常有价值的实践案例，可以帮助他们更好地理解游标的工作原理及其应用。同时...

    SQL行转列、列转行的简单实现

    在SQL数据库操作中，行转列和列转行是数据处理和分析中常见的需求，尤其在报表制作和数据展示时。这两种转换可以帮助我们更有效地组织和呈现数据。本篇文章将详细探讨MySQL中如何实现行转列和列转行的操作。 **行转...

    SQL基础教程之行转列Pivot函数

    例如，在Oracle中，你可以这样实现行转列： ```sql SELECT * FROM DailyIncome PIVOT ( SUM(IncomeAmount) FOR IncomeDay IN ('FRI', 'MON', 'SUN', 'THU', 'TUE', 'WED', 'SAT') ); ``` 这里，Oracle的`PIVOT`...

    sql中把指定的列作为标题列

    在SQL查询语言中，将指定的列作为标题列是一种数据透视技术的应用，这种技术主要用于将行数据转换为列数据，从而实现数据结构的重塑，便于数据分析和报告制作。这一过程通常被称为“列转行”或“透视”，在SQL中可以...

    selectcase实现行转列.txt

    本sql实现了数据库行转列的汇总查询，一条sql搞定全部计算

    sql server 2008 将一列值转换成一个字符串

    在SQL Server 2008中，将一列值转换为一个字符串是一个常见的需求，特别是在进行数据汇总或生成报告时。本篇文章将详细介绍如何利用SQL Server 2008中的功能来实现这一需求，并深入探讨背后的原理和技术要点。 ### ...

Magicbox
Global site tag (gtag.js) - Google Analytics