金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 中的行列转换小结,Sql基本学习二

中的行列转换小结,Sql基本学习二

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-22 11:09

使用PIVOT行转列

1. 介绍说明

前段时间组内的小伙伴在升级维护项目中,经常涉及一些复杂的数据转换问题,让我去看下有些地方怎么处理,我发现好多都是涉及到行列转换的问题,处理起来经常会比较麻烦,借此也总结一下,方便以后的查阅使用。该总结参照了网上的一些资料,也做了一些变动,如有更好的方法也欢迎指出。

演示的脚本见 3.测试数据脚本

 

1.行列转换

1.建表及插入数据

2. 例子演示

2.1 实现行转列

(1) Case WHEN 实现行转列 

图片 1图片 2

/*-----1.1 Case WHEN 实现行转列----------*/

--(1)静态SQL
SELECT [姓名],
 max(CASE 课程 WHEN '语文' THEN 分数 ELSE 0 end) AS 语文,
 max(CASE 课程 WHEN '数学' THEN 分数 ELSE 0 end)AS 数学,
 max(CASE 课程 WHEN '物理' THEN 分数 ELSE 0 end)AS 物理,
 SUM(分数) AS 总分,
 AVG(分数) AS 平均分
FROM tbScore GROUP BY [姓名]

--(2)动态SQL
DECLARE @sql VARCHAR(500)
SET @sql = 'SELECT [姓名]'
SELECT  @sql = @sql + ',MAX(CASE [课程] WHEN ''' + [课程] + ''' THEN [分数] ELSE 0 END)[' + [课程] + ']'
FROM    ( 
            SELECT DISTINCT [课程] FROM tbScore
        ) T1
--同FROM tbScore  GROUP BY [课程],默认按课程名排序
SET @sql = @sql + ' FROM tbScore GROUP BY [姓名]'
PRINT '@sql: ' + @sql
EXEC(@sql)

View Code

 图片 3

 

 (2) PIVOT 实现行转列,其中的NULL值发现还不好处理为0

图片 4图片 5

--(1)静态SQL
SELECT  [姓名] ,
        [语文] ,
        [数学] ,
        [物理]
FROM    ( SELECT    [分数] ,
                    [课程] ,
                    [姓名]
          FROM      tbScore
        ) AS SourceTable PIVOT ( AVG([分数]) FOR [课程] IN ( 语文, 数学, 物理 ) ) T


--(2)动态SQL
DECLARE @sql2 VARCHAR(8000)
SET @sql2 = ''
SELECT @sql2 = @sql2 + ',' + [课程] FROM dbo.tbScore GROUP BY [课程]
--STUFF: 删除指定长度的字符,并在指定的起点处插入另一组字符。
SET @sql2= STUFF(@sql2,1,1,'')  --去掉首个','
SET @sql2 = 'SELECT [姓名],' + @sql2 + ' FROM (SELECT [分数],[课程],[姓名] FROM tbScore ) AS SourceTable PIVOT ( AVG([分数]) FOR [课程] IN ( ' + @sql2 + ') ) T'
PRINT @sql2
EXEC(@sql2)

View Code

图片 6

 

2.1 实现转行

 (1) UNION 实现列转行

图片 7图片 8

--(1)静态SQL
SELECT * FROM (
    SELECT [姓名],'语文' AS 课程,[语文] AS 分数 ,[日期] FROM tbScoreNew
    UNION ALL
    SELECT [姓名],'数学' AS 课程,[数学] AS 分数 ,[日期] FROM tbScoreNew
    UNION ALL
    SELECT [姓名],'物理' AS 课程,[物理] AS 分数 ,[日期] FROM tbScoreNew
) T ORDER BY [姓名]

--(2)动态SQL
DECLARE @sql3 VARCHAR(8000)
SELECT @sql3 = ISNULL(@sql3 + ' UNION ALL ','') + ' SELECT [姓名],' + QUOTENAME(name,'''') + ' AS 课程,' + QUOTENAME(name) + ',[日期] FROM tbScoreNew'
FROM sys.columns 
WHERE object_id = OBJECT_ID('tbScoreNew') AND  name NOT IN ('姓名','日期')
SET @sql3 = 'SELECT * FROM ( ' + @sql3  + ' ) T ORDER BY [姓名]'
PRINT @sql3
EXEC (@sql3)

View Code

图片 9

 

 (2) UNPIVOT 实现列转行

图片 10图片 11

--(1)静态SQL
SELECT * FROM (
    SELECT [姓名],[日期],[语文],[数学],[物理] FROM dbo.tbScoreNew
) T UNPIVOT ([分数] FOR [课程] IN ([语文],[数学],[物理])) T2
ORDER BY [姓名]


--(2)动态SQL
DECLARE @sql4 VARCHAR(8000)
SELECT @sql4 = ISNULL(@sql4 + ',','') + QUOTENAME(name)
FROM sys.columns 
WHERE object_id = OBJECT_ID('tbScoreNew') AND  name NOT IN ('姓名','日期')
SET @sql4 = 'SELECT * FROM ( SELECT [姓名],[日期],' + @sql4 + ' FROM dbo.tbScoreNew ) T UNPIVOT ([分数] FOR [课程] IN ('+ @sql4 +')) T2 ORDER BY [姓名]'
PRINT @sql4
EXEC (@sql4)

View Code

图片 12

 

2.3 动态增加列实现行转列 

这个参照部门小伙伴的项目上的要求写的一个例子, 由于涉及的转换列同时有多个字段,用上面的行列转换处理起来都很不方便,所以采用比较普通的动态增加列的方式处理

测试数据脚本为附件脚本中的 “3.动态增加列实现行转列" 脚本

要求: 将【部门预算】、【实际预算】、【剩余预算】按照年份横向统计显示,且统计数据按部门、项目分组显示

图片 13图片 14

CREATE TABLE #tmpYear
(
    [YEAR] INT,
    ID INT IDENTITY
)

--保存最终结果
CREATE TABLE #tmpResult
(
    ID INT IDENTITY,
    DeptCode VARCHAR(20),--部门编码
    DeptName NVARCHAR(100), --部门名称
    ProCode VARCHAR(20),--项目编码
    ProName NVARCHAR(100),--项目名称
    KeyCode VARCHAR(50)
)
GO

--1.写入分组数据
INSERT INTO #tmpResult( DeptCode ,DeptName , ProCode ,ProName,KeyCode)
SELECT DeptCode,MAX(DeptName), ProCode,MAX(ProName),DeptCode + '_' + ProCode FROM tbDeptBudget GROUP BY DeptCode,ProCode

--2.计算预算结果数据
--写入年份数据
INSERT INTO #tmpYear SELECT DISTINCT Year FROM dbo.tbDeptBudget

DECLARE @SQL VARCHAR(5000)
DECLARE @ColName1 VARCHAR(50)
DECLARE @ColName2 VARCHAR(50)
DECLARE @ColName3 VARCHAR(50)
DECLARE @Year INT
DECLARE @ID INT
DECLARE @RowNum INT
SET @Year = 0
SET @ID = 1
SET @RowNum = (SELECT COUNT(0) FROM #tmpYear)
WHILE @ID <= @RowNum
BEGIN
    SET @Year = (SELECT [YEAR] FROM #tmpYear WHERE ID = @ID)    
    SET @ColName1 = 'Bduget_' + CAST(@Year AS VARCHAR(10))
    SET @ColName2 = 'Fact_' + CAST(@Year AS VARCHAR(10))
    SET @ColName3 = 'Remain_' + CAST(@Year AS VARCHAR(10))

    --增加动态列
    SET @SQL = 'ALTER TABLE #tmpResult ADD ' + @ColName1 + ' Decimal(18,2)'
              + 'ALTER TABLE #tmpResult ADD ' + @ColName2 + ' Decimal(18,2)'
              + 'ALTER TABLE #tmpResult ADD ' + @ColName3 + ' Decimal(18,2)'
    EXEC(@SQL)

    --写入动态列数据
    SET @SQL = 'UPDATE T SET ' + @ColName1 + ' = S.BudgetAmount,' + @ColName2 + ' = S.FactAmount,'+ @ColName3 + ' = S.RemainAmount '
        + ' FROM #tmpResult T INNER JOIN ( '
        + ' SELECT (DeptCode + ' + QUOTENAME('_','''') +' + ProCode) AS KeyCode,MAX(BudgetAmount)AS BudgetAmount ,MAX(FactAmount)AS FactAmount,MAX(RemainAmount)AS RemainAmount '
        + ' FROM dbo.tbDeptBudget WHERE Year= ' + CAST (@Year AS VARCHAR(10))
        + ' GROUP BY DeptCode,ProCode '
        + ') S ON T.KeyCode = S.KeyCode '

    PRINT @SQL
    EXEC(@SQL)

    SET @ID = @ID  + 1
END

--3.返回结果
SELECT * FROM #tmpResult

--4.清理临时表
IF OBJECT_ID('tempdb..#tmpYear') IS NOT NULL
BEGIN
    DROP TABLE #tmpYear
END
IF OBJECT_ID('tempdb..#tmpResult') IS NOT NULL
BEGIN
    DROP TABLE #tmpResult
END

View Code

 图片 15

 

图片 16图1

图片 17图片 18

3. 测试数据脚本

图片 19图片 20

/*-----1.行转列的测试数据--------------------------*/
IF OBJECT_ID('tbScore') IS NOT NULL 
    DROP TABLE tbScore

GO

CREATE TABLE tbScore
    (
      姓名 VARCHAR(10) ,
      课程 VARCHAR(10) ,
      分数 INT,
      日期 DATETIME
    )
GO

INSERT  INTO tbScore VALUES  ( '张三', '语文', 74,GETDATE() )
--INSERT  INTO tbScore VALUES  ( '张三', '数学', 83 ,GETDATE() )
INSERT  INTO tbScore VALUES  ( '张三', '物理', 93 ,GETDATE() )
INSERT  INTO tbScore VALUES  ( '李四', '语文', 74 ,GETDATE() )
INSERT  INTO tbScore VALUES  ( '李四', '数学', 84 ,GETDATE() )
INSERT  INTO tbScore VALUES  ( '李四', '物理', 94 ,GETDATE() )
GO

/*-----2.列转行的测试数据--------------------------*/
IF OBJECT_ID('tbScoreNew') IS NOT NULL 
    DROP TABLE tbScoreNew

GO

CREATE TABLE tbScoreNew(
      姓名 VARCHAR(10) ,
      语文 INT,
      数学 INT,
      物理 INT,
      日期 DATETIME
    )
GO

INSERT  INTO tbScoreNew VALUES  ( '李四', 74,84,94,GETDATE() )
INSERT  INTO tbScoreNew VALUES  ( '张三', 74,83,93,GETDATE() )
GO


/*-----3.动态增加列实现行转列(模拟组内项目要求)--------------------------*/
IF OBJECT_ID('tbDeptBudget') IS NOT NULL 
    DROP TABLE tbDeptBudget

GO
--部门预算
CREATE TABLE tbDeptBudget
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    DeptCode VARCHAR(20),--部门编码
    DeptName NVARCHAR(100), --部门名称
    ProCode VARCHAR(20),--项目编码
    ProName NVARCHAR(100),--项目名称
    Year INT, --年度
    BudgetAmount DECIMAL(18,2), --预算金额
    FactAmount DECIMAL(18,2), --实际金额
    RemainAmount DECIMAL(18,2), --剩余金额
    CreateTime DATETIME  --创建时间
)
GO

INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('人事部','010000','01','差旅费',2014,100000.00,80000.00,20000.00,GETDATE());
INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('人事部','010000','01','差旅费',2015,110000.00,90000.00,50000.00,GETDATE());
INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('人事部','010000','01','差旅费',2016,120000.00,100000.00,80000.00,GETDATE());
INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('人事部','010000','02','办公用品',2015,200000.00,150000.00,10000.00,GETDATE());
INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('人事部','010000','02','办公用品',2016,160000.00,120000.00,80000.00,GETDATE());
INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('财务部','020000','02','办公用品',2014,50000.00,40000.00,0.00,GETDATE());
INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('财务部','020000','02','办公用品',2015,50000.00,50000.00,10000.00,GETDATE());
INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('财务部','020000','02','办公用品',2016,60000.00,50000.00,40000.00,GETDATE());
INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('财务部','020000','03','采购费',2016,100000.00,80000.00,60000.00,GETDATE());

View Code

 

测试脚本附件

把图1的行转换为图2的列

 1 USE [AdventureDB]
 2 GO
 3 /****** Object:  Table [dbo].[Score]    Script Date: 11/25/2016 4:30:50 PM ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 
 7 SET QUOTED_IDENTIFIER ON
 8 GO
 9 
10 CREATE TABLE [dbo].[Score]([Name] [varchar](50) NULL,[Subject] [varchar](50) NULL,[Score] FLOAT NULL) ON [PRIMARY]
11 GO
12 
13 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Jack', N'linguistic', 65)
14 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Tom', N'linguistic', 56)
15 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Lina', N'linguistic', 84)
16 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Jack', N'Mathematics', 100)
17 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Tom', N'Mathematics', 82)
18 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Lina', N'Mathematics', 67)
19 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Jack', N'English', 82)
20 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Tom', N'English', 54)
21 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Lina', N'English', 76)
22 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'James', N'Other', 52)
23 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Tom', N'Other', 99)
24 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Lina', N'Other', 79)
25 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Kobe', N'linguistic', 65)
26 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'James', N'linguistic', 76)
27 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Kidd', N'linguistic', 86)
28 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'James', N'Mathematics', 70)
29 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Kobe', N'Mathematics', 92)
30 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Kidd', N'Mathematics', 70)
31 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Kobe', N'English', 86)
32 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Kidd', N'English', 85)
33 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'James', N'English', 66)
34 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Jack', N'Other', 77)
35 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Kobe', N'Other', 97)
36 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Kidd', N'Other', 93)

4. 参考资料

   

 

 

图片 21

View Code

使用2005自带函数 PIVOT

图片 22

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

2.使用CASE语句查询

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

图片 23图片 24

完整语法:

 1 USE [AdventureDB]
 2 GO
 3 
 4 /****** Object:  StoredProcedure [dbo].[CaseSelect]    Script Date: 12/02/2016 00:47:02 ******/
 5 SET ANSI_NULLS ON
 6 GO
 7 
 8 SET QUOTED_IDENTIFIER ON
 9 GO
10 
11 CREATE procedure [dbo].[CaseSelect] AS
12 
13 BEGIN
14 
15    SELECT [Name],
16         SUM (case when [Subject] = 'English' then [Score] else 0 end) English,
17         SUM (case when [Subject] = 'linguistic' then [Score] else 0 end) Linguistic,
18         SUM (case when [Subject] = 'Mathematics' then [Score] else 0 end) Mathematics,
19         SUM (case when [Subject] = 'Other' then [Score] else 0 end) Other,
20         AVG ([Score]) Average
21     FROM [dbo].[score] GROUP BY [Name] ORDER BY [Name] DESC
22     
23 END
24 
25 GO

table_source

View Code

PIVOT(

图片 25

聚合函数(value_column)

3.使用PIVOT行转列

FOR pivot_column

图片 26图片 27

IN(<column_list>)

 1 USE [AdventureDB]
 2 GO
 3 
 4 /****** Object:  StoredProcedure [dbo].[Pivot]    Script Date: 12/02/2016 01:07:27 ******/
 5 SET ANSI_NULLS ON
 6 GO
 7 
 8 SET QUOTED_IDENTIFIER ON
 9 GO
10 
11 CREATE procedure [dbo].[Pivot]
12     @NumberOfStudents int = 5
13 AS
14 
15 IF @NumberOfStudents < 1 or @NumberOfStudents > 10
16     RAISERROR('@NumberOfStudents must be between 1 and 10', 11, 1);
17 ELSE
18     SELECT top(@NumberOfStudents)
19         p.[name],
20         p.English,
21         p.linguistic,
22         p.Mathematics,
23         p.Other,
24         (p.English + p.linguistic+p.Mathematics + p.Other)/4 AS Average
25     FROM [dbo].[score] PIVOT (SUM (score) FOR [subject] IN (English,linguistic,Mathematics,Other) ) AS P
26     ORDER BY  p.[name] DESC
27     
28 RETURN;
29 
30 GO

)

View Code

 

4.PIVOT动态获取列

UNPIVOT用于将列明转为列值(即列转行),在SQL Server 2000可以用UNION来实现

图片 28图片 29

完整语法:

 1 USE [AdventureDB]
 2 GO
 3 
 4 /****** Object:  StoredProcedure [dbo].[Pivot_DynamicColumn]    Script Date: 12/02/2016 01:31:30 ******/
 5 SET ANSI_NULLS ON
 6 GO
 7 
 8 SET QUOTED_IDENTIFIER ON
 9 GO
10 
11 CREATE procedure [dbo].[Pivot_DynamicColumn] AS
12 
13 BEGIN
14     DECLARE @ColumnNames NVARCHAR(Max)
15     DECLARE @AverageScore NVARCHAR(Max)
16     DECLARE @ColumnCount int
17  
18     SET @ColumnNames=''
19     SET @AverageScore = ''
20     SET @ColumnCount = ''
21     
22     SELECT @ColumnCount = COUNT(DISTINCT [Subject]) FROM [Score]
23  
24     SELECT
25        @ColumnNames = @ColumnNames + '[' + [Subject] + '],',
26        @AverageScore = @AverageScore + '[' + [Subject] + ']+'
27     FROM
28        (
29        SELECT DISTINCT [Subject] FROM [Score]
30        ) t
31  
32     SET @ColumnNames= LEFT(@ColumnNames, LEN(@ColumnNames)-1)
33     SET @AverageScore= LEFT(@AverageScore, LEN(@AverageScore)-1)
34  
35     DECLARE @selectSQL  NVARCHAR(Max)
36  
37     SET @selectSQL=
38     'SELECT [name],{0},({1})/{2} as Average FROM
39        [dbo].[score]
40      Pivot(SUM(score) For [subject] in ({0})) AS p
41        ORDER BY  p.[name] DESC'
42  
43     SET @selectSQL= REPLACE(@selectSQL,'{0}',@ColumnNames)
44     SET @selectSQL= REPLACE(@selectSQL,'{1}',@AverageScore)
45     SET @selectSQL= REPLACE(@selectSQL,'{2}',@ColumnCount)
46  
47     EXEC sp_executesql @selectSQL
48 END
49 
50 GO

table_source

View Code

UNPIVOT(

使用UNPIVOT列转行

value_column

1.建表及插入数据

FOR pivot_column

图片 30图片 31

IN(<column_list>)

 1 USE [AdventureDB]
 2 GO
 3 
 4 /****** Object:  Table [dbo].[ScorePivot]    Script Date: 2016/12/6 17:38:48 ******/
 5 SET ANSI_NULLS ON
 6 GO
 7 
 8 SET QUOTED_IDENTIFIER ON
 9 GO
10 
11 CREATE TABLE [dbo].[ScorePivot](
12     [Name] [varchar](50) NULL,
13     [English] [varchar](50) NULL,
14     [Linguistic] [varchar](50) NULL,
15     [Mathematics] [varchar](50) NULL,
16     [Other] [varchar](50) NULL) ON [PRIMARY]
17 GO
18 
19 INSERT INTO [dbo].[ScorePivot] ([Name], [English], [Linguistic], [Mathematics], [Other]) VALUES (N'Tom', N'54', N'56', N'82', N'99')
20 INSERT INTO [dbo].[ScorePivot] ([Name], [English], [Linguistic], [Mathematics], [Other]) VALUES (N'Lina', N'76', N'84', N'67', N'79')
21 INSERT INTO [dbo].[ScorePivot] ([Name], [English], [Linguistic], [Mathematics], [Other]) VALUES (N'Kobe', N'86', N'65', N'92', N'97')
22 INSERT INTO [dbo].[ScorePivot] ([Name], [English], [Linguistic], [Mathematics], [Other]) VALUES (N'Kidd', N'85', N'86', N'70', N'93')
23 INSERT INTO [dbo].[ScorePivot] ([Name], [English], [Linguistic], [Mathematics], [Other]) VALUES (N'James', N'66', N'76', N'70', N'52')
24 INSERT INTO [dbo].[ScorePivot] ([Name], [English], [Linguistic], [Mathematics], [Other]) VALUES (N'Jack', N'82', N'65', N'100', N'77')

)

View Code

图片 32图片 33View Code

图片 34

  WITH  ProductTable
          AS ( SELECT   VPIV.ProductID ,
                        DF.DataID ,
                        DirectionDrugNum = SUM(VCPSD.DirectionDrugNum)
               FROM     dbo.VProductInfoView VPIV ,
                        dbo.[UF_XA_GetDatePartTable_21](2, '2011-01-01',
                                                        '2011-12-31') DF ,
                        dbo.VCustomerProStockDetail VCPSD
               WHERE    VPIV.ProductID = VCPSD.ProductID
                        AND VCPSD.CustomerStockType = 0
                        AND VCPSD.BizDate >= DF.BeginDate
                        AND VCPSD.BizDate < DF.EndDate
               GROUP BY VPIV.ProductID ,
                        DF.DataID
             )
    SELECT  ProductID ,
            [1] AS 一月 ,
            [2] AS 二月 ,
            [3] AS 三月 ,
            [4] AS 四月 ,
            [5] AS 五月 ,
            [6] AS 六月 ,
            [7] AS 七月 ,
            [8] AS 八月 ,
            [9] AS 九月 ,
            [10] AS 十月 ,
            [11] AS 十一月 ,
            [12] AS 十二月 ,
            ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0) + ISNULL([4], 0)
            + ISNULL([5], 0) + ISNULL([6], 0) + ISNULL([7], 0) + ISNULL([8], 0)
            + ISNULL([9], 0) + ISNULL([10], 0) + ISNULL([11], 0) + ISNULL([12],
                                                              0) AS 累计
    FROM    ( SELECT    VPIV.ProductID ,
                        VPIV.ProName ,
                        VPIV.ProStandard ,
                        VPIV.FactoryName ,
                        PT.DataID ,
                        PT.DirectionDrugNum
              FROM      dbo.VProductInfoView VPIV
                        LEFT JOIN ProductTable PT ON VPIV.ProductID = PT.ProductID
            ) A PIVOT  
   ( SUM(A.DirectionDrugNum) FOR A.DataID IN ( [1], [2], [3], [4], [5], [6],
                                               [7], [8], [9], [10], [11], [12] ) ) AS pvt ;

2.使用UNPIVOT列转行

实现效果:

图片 35图片 36

图片 37

USE [AdventureDB]
GO

/****** Object:  StoredProcedure [dbo].[UNPivot]    Script Date: 2016/12/6 17:49:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[UNPivot]  AS

    SELECT
        [Name], [Subject], [Score]
    FROM 
    (
        SELECT [Name], [English],[Linguistic],[Mathematics], [Other] FROM [dbo].[ScorePivot]
    ) data
    UNPIVOT
    (
        [Score] FOR [Subject] IN 
        (
            [English], [Linguistic], [Mathematics], [Other]
        ) 
    )AS nupvt

GO

View Code

图片 38

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:中的行列转换小结,Sql基本学习二

关键词: