Server之纵表与横表互转,经典行专列
分类:数据库

1,纵表转横表

T-SQL 经典编程

sqlserver的行转列 列转行问题

  阅读目录

纵表结构 Table_A:

说明:本实例是以 SQL Server 2005 为运行环境的。

行转列:
1 使用Case when 方式

  一:前言

澳门新萄京 1

准备工作:创建一个名为 DB 的数据库(CREATE DATABASE DB)。

CREATE TABLE [StudentScores]
(
[UserName] NVARCHAR(20), --学生姓名
[Subject] NVARCHAR(30), --科目
[Score] FLOAT, --成绩
)

  二:第一次思考

转换后的结构:

 

INSERT INTO [StudentScores] SELECT 'Nick', '语文', 80

  三:第二次思考

澳门新萄京 2

一、T-SQL 行转列

INSERT INTO [StudentScores] SELECT 'Nick', '数学', 90

  四:第三次思考

纵表转横表的SQL示例:

1、创建如下表

INSERT INTO [StudentScores] SELECT 'Nick', '英语', 70

  一:前言

SELECT  Name ,
        SUM(CASE WHEN Course = N'语文' THEN Grade
                 ELSE 0
            END) AS Chinese ,
        SUM(CASE WHEN Course = N'数学' THEN Grade
                 ELSE 0
            END) AS Mathematics ,
        SUM(CASE WHEN Course = N'英语' THEN Grade
                 ELSE 0
            END) AS English
FROM    dbo.Table_A
GROUP BY Name

CREATE  TABLE [Scores]  (

INSERT INTO [StudentScores] SELECT 'Nick', '生物', 85

  在我们做的一些项目中经常会碰到把行转化为列的问题,那么我们今天就来探讨一下,我们怎么样把表1转化为表2的格式

2,横表转纵表

      [ID] INT IDENTITY(1,1),    --自增标识

INSERT INTO [StudentScores] SELECT 'Kent', '语文', 80

    澳门新萄京 3

横表结构Table_B:

      [StuNo] INT,                 --学号

INSERT INTO [StudentScores] SELECT 'Kent', '数学', 90

        图一

澳门新萄京 4

      [Subject] NVARCHAR(30), --科目

INSERT INTO [StudentScores] SELECT 'Kent', '英语', 70

  澳门新萄京 5

转换后的表结构:

      [Score] FLOAT                --成绩

INSERT INTO [StudentScores] SELECT 'Kent', '生物', 85

        图二

澳门新萄京 6

)

SELECT * FROM [StudentScores]
--如果我想知道每位学生的每科成绩,而且每个学生的全部成绩排成一行,这样方便
我查看、统计,导出数据

  二:第一次思考

横表转纵表的SQL示例:

GO

SELECT
UserName,
MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文',
MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学',
MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) AS '英语',
MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) AS '生物'
FROM dbo.[StudentScores]
GROUP BY UserName
2 使用PIVOT 、UNPIVOT运算符

  可以看得出来,表2列是由表1的数据行转化而来的,只有Name列是表1中的列,一步一步的来,其他的列难搞,如果表2只有1列Name的话,那么简单了,不就是一个简单的分组

SELECT  Name ,
        'Chinese' AS Course ,
        Chinese AS Score
FROM    dbo.Table_B
UNION ALL
SELECT  Name ,
        'Mathematics' AS Course ,
        Mathematics AS Score
FROM    dbo.Table_B
UNION ALL
SELECT  Name ,
        'English' AS Course ,
        English AS Score
FROM    dbo.Table_B
ORDER BY Name ,
        Course DESC

 

--方式一
DECLARE @cmdText VARCHAR(8000);
DECLARE @tmpSql VARCHAR(8000);

  SELECT [Name] FROM NameAndSubjectAndGrade GROUP BY [Name]

 

INSERT INTO [Scores]

SET @cmdText = 'SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS
CreateTime,' CHAR(10);
SELECT @cmdText = @cmdText ' CASE PayType WHEN ''' PayType ''' THEN
SUM(Money) ELSE 0 END AS ''' PayType

  现在SQL语句的架子搭起来了,无论以后如何变化,分组是少不了的

SELECT 100, '语文', 80 UNION

  • ''',' CHAR(10) FROM (SELECT DISTINCT PayType FROM
    Inpours ) T

    澳门新萄京 7

SELECT 100, '数学', 75 UNION

SET @cmdText = LEFT(@cmdText, LEN(@cmdText) -2) --注意这里,如果没有加CHAR
(10) 则用LEFT(@cmdText, LEN(@cmdText) -1)

  二:第二次思考

SELECT 100, '英语', 70 UNION

SET @cmdText = @cmdText ' FROM Inpours GROUP BY CreateTime, PayType
';

  现在我们想在这个结果集中再添加1列,多了我们不加,因为你不论是能处理语文,还是数学,还是英语列,那么其他的列只要原样照抄就可以了,我们就只在现在的基础上添加一个语文列吧

SELECT 100, '生物', 85 UNION

SET @tmpSql ='SELECT CreateTime,' CHAR(10);
SELECT @tmpSql = @tmpSql ' ISNULL(SUM(' PayType '), 0) AS '''
PayType ''',' CHAR(10)
FROM (SELECT DISTINCT PayType FROM Inpours ) T

  SELECT [Name], CASE WHEN Subject = '语文' THEN Grade END FROM NameAndSubjectAndGrade GROUP BY [Name]

SELECT 101, '语文', 80 UNION

SET @tmpSql = LEFT(@tmpSql, LEN(@tmpSql) -2) ' FROM (' CHAR(10);

  澳门新萄京 8

SELECT 101, '数学', 90 UNION

SET @cmdText = @tmpSql @cmdText ') T GROUP BY CreateTime ';
PRINT @cmdText
EXECUTE (@cmdText);

  我们看错误提示,Subject和Grade列要在聚合函数或者GROUP BY 子句中,那么我们先把Subject和Grade列放在GROUP BY 子句中

SELECT 101, '英语', 70 UNION

--方式二
SELECT
CreateTime,
ISNULL(SUM([支付宝]) , 0) AS [支付宝] ,
ISNULL(SUM([手机短信]) , 0) AS [手机短信] ,
ISNULL(SUM([工商银行卡]), 0) AS [工商银行卡] ,
ISNULL(SUM([建设银行卡]), 0) AS [建设银行卡]
FROM
(
SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,
CASE PayType WHEN '支付宝' THEN SUM(Money) ELSE 0 END AS '支
付宝' ,
CASE PayType WHEN '手机短信' THEN SUM(Money) ELSE 0 END AS '手
机短信',
CASE PayType WHEN '工商银行卡' THEN SUM(Money) ELSE 0 END AS '工
商银行卡',
CASE PayType WHEN '建设银行卡' THEN SUM(Money) ELSE 0 END AS '建
设银行卡'
FROM Inpours
GROUP BY CreateTime, PayType
) T
GROUP BY CreateTime

  SELECT [Name],CASE WHEN Subject = '语文' THEN Grade END FROM NameAndSubjectAndGrade GROUP BY [Name],Subject,Grade

SELECT 101, '生物', 85

--方式三
SELECT
CreateTime, [支付宝] , [手机短信],
[工商银行卡] , [建设银行卡]
FROM
(
SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,PayType,
Money
FROM Inpours
) P
PIVOT (
SUM(Money)
FOR PayType IN
([支付宝], [手机短信], [工商银行卡], [建设银行卡])
) AS T
ORDER BY CreateTime

  数据倒是有了,可是行多了点,看来只能从聚合入手

 

 

  澳门新萄京 9

CREATE  TABLE [Student]  (

--列转行 主要通过 Union all ,max 来实现

  三:第三次思考

      [ID] INT IDENTITY(100,1),     --自增标识,学号

CREATE TABLE ProgrectDetail
(
ProgrectName NVARCHAR(20), --工程名称
OverseaSupply INT, --海外供应商供给数量
NativeSupply INT, --国内供应商供给数量
SouthSupply INT, --南方供应商供给数量
NorthSupply INT --北方供应商供给数量
)

  我们从聚合函数入手

      [StuName] NVARCHAR(30),         --姓名

INSERT INTO ProgrectDetail
SELECT 'A', 100, 200, 50, 50
UNION ALL
SELECT 'B', 200, 300, 150, 150
UNION ALL
SELECT 'C', 159, 400, 20, 320
UNION ALL
SELECT 'D', 250, 30, 15, 15

  SELECT [Name],

      [Sex] NVARCHAR(30),             --性别

-- 使用 Union all 和max
SELECT ProgrectName, 'OverseaSupply' AS Supplier,
MAX(OverseaSupply) AS 'SupplyNum'
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, 'NativeSupply' AS Supplier,
MAX(NativeSupply) AS 'SupplyNum'
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, 'SouthSupply' AS Supplier,
MAX(SouthSupply) AS 'SupplyNum'
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, 'NorthSupply' AS Supplier,
MAX(NorthSupply) AS 'SupplyNum'
FROM ProgrectDetail
GROUP BY ProgrectName

  SUM(CASE WHEN Subject = '语文' THEN Grade ELSE 0 END) AS YuWen

      [Age] CHAR(2)                    --年龄

--用UNPIVOT方式
SELECT ProgrectName,Supplier,SupplyNum
FROM
(
SELECT ProgrectName, OverseaSupply, NativeSupply,
SouthSupply, NorthSupply
FROM ProgrectDetail
)T
UNPIVOT
(
SupplyNum FOR Supplier IN
(OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
) P

  FROM NameAndSubjectAndGrade GROUP BY [Name]

)

 

  澳门新萄京 10

GO

  那么其他几列也就好办了,我们发挥我们的Ctr C,Ctr V

 

  SELECT [Name],

INSERT INTO [Student]

  SUM(CASE WHEN Subject = '语文' THEN Grade END) AS YuWen,

SELECT '张三', '男', 80 UNION

  SUM(CASE WHEN Subject = '数学' THEN Grade END) AS ShuXue,

SELECT '李四', '女', 75

  SUM(CASE WHEN Subject = '英语' THEN Grade END) AS YingYu

 

  FROM NameAndSubjectAndGrade GROUP BY [Name]

两表的数据如下图:

  澳门新萄京 11

 

  四:第四次思考

2、通过CASE…WHEN 语句和GROUP BY…聚合函数 来实现行转列

  马六同学成天逃课,数学和英语就没参加考试,那么我们不应该显示'NULL',应该显示为'0',可是结果有NULL,那不太好,我们来把CASE WHEN THEN END写完,在此之间加个ELSE 0

SELECT

  SELECT [Name],

      StuNo AS '学号',

  SUM(CASE WHEN Subject = '语文' THEN Grade ELSE 0 END) AS YuWen,

      MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文',

  SUM(CASE WHEN Subject = '数学' THEN Grade ELSE 0 END) AS ShuXue,

      MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学',

  SUM(CASE WHEN Subject = '英语' THEN Grade ELSE 0 END) AS YingYu 

      MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) AS '英语',

  FROM NameAndSubjectAndGrade GROUP BY [Name]

      MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) AS '生物',

  最后完成了

      SUM(Score) AS '总分',

  澳门新萄京 12

      AVG(Score) AS '平均分'

  

FROM dbo.[Scores]

GROUP BY StuNo

ORDER BY StuNo ASC

结果如下图:

 

3、通过 JOIN…ON 实现两表联接,显示出学生姓名

SELECT

      MAX(StuNo) AS '学号',

      StuName AS '姓名',

      MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文',

      MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学',

      MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) AS '英语',

      MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) AS '生物',

      SUM(Score) AS '总分',

      AVG(Score) AS '平均分'

FROM dbo.[Scores] A join [Student] B on (A.StuNo=B.ID)

GROUP BY StuName

ORDER BY StuName ASC

结果如下图:

 

4、通过 PIVOT 实现行转列

SELECT

StuNo AS '学号',

StuName AS '姓名',

AVG(语文) AS '语文',

AVG(数学) AS '数学',

AVG(英语) AS '英语',

AVG(生物) AS '生物'

FROM [Scores]

PIVOT(

    AVG(Score) FOR Subject IN

    (语文,数学,英语,生物)

) AS NewScores

JOIN [Student] ON (NewScores.StuNo=Student.ID)

GROUP BY NewScores.StuNo,StuName

ORDER BY StuName ASC

结果如下图:

 

二、T-SQL列转行

1、创建数据表并插入 4 条数据

CREATE  TABLE [StudentScores]  (

      [ID] INT IDENTITY(1,1),        --自增标识

      [StuNo] INT,                     --学号

澳门新萄京,      [Chinese] NVARCHAR(30),     --语文

      [Mathematics] NVARCHAR(30),   --数学

      [English] NVARCHAR(30),     --英语

      [Biology] NVARCHAR(30)         --生物

)

GO

 

INSERT INTO [StudentScores]

SELECT 100, 80, 85, 75, 80 UNION

SELECT 101, 90, 80, 70, 75 UNION

SELECT 102, 95, 90, 80, 70 UNION

SELECT 103, 60, 70, 80, 85

数据如下图:

 

2、通过 UNION ALL…MAX 实现列转行

SELECT StuNo, 'Chinese' AS Subject, 

        MAX(Chinese) AS 'Score'

FROM [StudentScores] 

GROUP BY [StuNo]

UNION ALL

SELECT StuNo, 'Mathematics' AS Subject, 

        MAX(Mathematics) AS 'Score'

FROM [StudentScores] 

GROUP BY [StuNo]

UNION ALL

SELECT StuNo, 'English' AS Subject, 

        MAX(English) AS 'Score'

FROM [StudentScores] 

GROUP BY [StuNo]

UNION ALL

SELECT StuNo, 'Biology' AS Subject, 

        MAX(Biology) AS 'Score'

FROM [StudentScores] 

GROUP BY [StuNo]

结果如下图:

      

3、用 UNPIVOT 实现列转行

       SELECT StuNo, Subject, Score

FROM [StudentScores]

UNPIVOT

(

    Score FOR Subject IN

    ([Chinese], [Mathematics], [English], [Biology])

) AS NewStudentScores

 

三、T-SQL 分页

1、创建数据库并插入 40000 条数据

CREATE  TABLE [Pagin]  (

      [ID] INT IDENTITY(1,1),    --自增标识

      [Number] INT,                --编号

      [Type] NVARCHAR(30),       --类型

      [Count] INT                  --数量

)

GO

 

declare @i int

set @i = 0

while(@i<10000)

begin

    INSERT INTO [Pagin] SELECT 10000 @i, 'A类', 80 @i%5

    INSERT INTO [Pagin] SELECT 10000 @i, 'B类', 60 @i

    INSERT INTO [Pagin] SELECT 10000 @i, 'C类', 70 @i%8

    INSERT INTO [Pagin] SELECT 10000 @i, 'D类', 90 @i%3

    set @i = @i 1

end

 

2、通过 TOP 实现分页

      方案一:两次 TOP 实现,原型如下

SELECT * FROM (

    SELECT TOP 5 * FROM (

        SELECT TOP 25 * FROM [Pagin] WHERE ID>0 ORDER BY ID ASC

    ) AS TEMPTABLE1 ORDER BY ID DESC

) AS TEMPTABLE2 ORDER BY ID ASC

 

    SELECT TOP 5 * FROM (

      SELECT TOP 25 * FROM [Pagin] WHERE ID>0 ORDER BY ID ASC

  ) AS TEMPTABLE1 ORDER BY ID DESC

说明:第一个 TOP 表示页面容量,第二个 TOP 表示页面容量*当前页码数。

弊病:    1、强制排序,否则不能分页,虽然目前基本上查询表都要排序。

2、排序字段不能有空值即null,否则分页结果不符实际情况。

3、多次order by 速度会快吗,有待我进一步大数据量测试。

      

         方案二:两次 TOP 基于NOT IN 实现,原型如下

select top 5 * from [Pagin]

    where ID not in (select top 25 ID from [Pagin] order by ID)

order by ID

说明:第一个 TOP 表示页面容量,第二个 TOP 表示页面容量*当前页码数。

弊病:    1、强制排序。

2、排序列必须是唯一列,否则分页情况不符实际。

3、使用not   in,速度慢。

      

      方案三:两次 TOP 基于MAX 或 MIN 实现,原型如下

         select top 5 * from [Pagin]

where ID > (select max(p.ID) from (select top 25 ID from [Pagin] order by id) as p)

order by ID

说明:第一个 TOP 表示页面容量,第二个 TOP 表示页面容量*当前页码数。

弊病:    1、强制排序。

2、排序列必须是唯一列,否则分页情况不符实际。

      

最后总结:在 sqlserver  分页中,第二第三种方案基本上是淘汰掉的,因为现在基本上什么表都是根据添加时间来排序,所以那两种方案没有用,真亏作者也敢发布出来,只有第一种方案还是稍微能用一下,但还是要复杂的拼sql 语句,不方便,要通用于所有表有点难度,象oracle 就很方便了,基于rownum ,传入一个sql 查询语句,这个查询语句爱怎么写就怎么写,反正保证它得到一个结果集就行,不像sqlserver又是要求唯一健又是要求必须排序,把一个结果集颠来倒去,不慢才怪呢。

      

方案一的简单存储过程如下:

CREATE PROCEDURE proc_page

    @pageIndex INT = 0,              --页索引

    @pageSize INT = 10,              --页大小

    @recordCount INT = 0 OUTPUT,    --返回纪录总数

    @pageCount INT = 0 OUTPUT       --返回页总数

AS

    DECLARE @sql NVARCHAR(1300)     --主sql语句

 

    --得到记录总数--

    BEGIN

        DECLARE @recordTotal INT

        SET @sql = N'SELECT @recordTotal=COUNT(ID) FROM [Pagin] WHERE ID>0'

        EXEC SP_EXECUTESQL @sql,N'@recordTotal INT OUTPUT',@recordTotal OUTPUT --@recordTotal = @recordCount OUTPUT

        SET @recordCount = @recordTotal

    END

 

    --计算页总数--

    IF(@recordCount%@pageSize=0)--如果记录总数除以页大小的余数为零

        SET @pageCount = @recordCount/@pageSize

    ELSE--如果记录总数除以页大小的余数不为零

        SET @pageCount = @recordCount/@pageSize 1

 

    --根据页索引执行分页查询--

    IF(@pageIndex<=1 OR @pageIndex>@pageCount)--如果是第一页,或者该页不存在,则默认也索引为1,即第一页

    BEGIN

        SET @pageIndex = 1

        SET @sql = 'SELECT TOP ' STR(@pageSize) ' * FROM [Pagin] WHERE ID>0 ORDER BY ID ASC'

        EXEC SP_Server之纵表与横表互转,经典行专列。EXECUTESQL @sql

    END

    ELSE--如果不是第一页,即其它页

    BEGIN

        SET @sql = 'SELECT * FROM (SELECT TOP ' STR(@pageSize) ' * FROM (SELECT TOP ' STR(@pageSize*@pageIndex) ' * FROM [Pagin] WHERE ID>0 ORDER BY ID ASC) AS TB1 ORDER BY ID DESC) AS TB2 ORDER BY ID ASC'

        EXEC SP_EXECUTESQL @sql

    END

 

    PRINT @sql--打印SQL 语句

GO

 

--测试

DECLARE @recordCount INT,@pageCount INT

EXEC   proc_page 0,5,@recordCount OUTPUT,@pageCount OUTPUT

PRINT @recordCount

PRINT @pageCount

 

注意:该存储过程是扩展的,还可以改为万能分页存储过程,只需再加上一些参数,再改改就好了。

本文由澳门新萄京发布于数据库,转载请注明出处:Server之纵表与横表互转,经典行专列

上一篇:澳门新萄京:Server的内存压力,品质计数器 下一篇:没有了
猜你喜欢
热门排行
精彩图文