mysql pivot_mysql 的行转列 PIVOT 的使用

语句：SELECT DataDate ,

PropertyText ,

DataValue

FROM RPT_ReportProperty p WITH ( NOLOCK ) JOIN RPT_ReportData d WITH ( NOLOCK ) ON p.PropertyID = d.PropertyID

WHERE d.ReportID = '3'

AND DataDate >= '06 25 2018 12:00AM'

AND DataDate < '07 16 2018 12:00AM'

AND DataParam = '日本'

结果：

语句：

SELECT [月份],[日期],[入库单量],[入库及时率],[延误入库单量],[异常入库单量]

FROM ( SELECT DataDate ,

PropertyText ,

DataValue

FROM RPT_ReportProperty p WITH ( NOLOCK ) JOIN RPT_ReportData d WITH ( NOLOCK ) ON p.PropertyID = d.PropertyID

WHERE d.ReportID = '3'

AND DataDate >= '06 25 2018 12:00AM'

AND DataDate < '07 16 2018 12:00AM'

AND DataParam = '日本'

) tb PIVOT ( MAX(DataValue) FOR PropertyText IN ( [月份],[日期],[入库单量],[入库及时率],[延误入库单量],[异常入库单量] ) ) a;

结果：

3.执行的语句

DECLARE @limitDay INT;

SET @limitDay = 35;

IF DATEDIFF(DAY, '2018-06-25 00:00:00', '2018-07-16 00:00:00') > @limitDay

BEGIN

RETURN;

END;

DECLARE @ReportID INT= 3;

DECLARE @BeginDateTime DATETIME= '2018-06-25 00:00:00';

DECLARE @EndDateTime DATETIME= '2018-07-16 00:00:00';

DECLARE @DataParam VARCHAR(50)= '日本';

EXEC dbo.Proc_GetReportData @ReportID, @BeginDateTime, @EndDateTime,

@DataParam;

存储过程：Proc_GetReportData

USE [Tr.TWX]

GO

/****** Object: StoredProcedure [dbo].[Proc_GetReportData] Script Date: 2018/7/16 15:08:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[Proc_GetReportData]

@ReportID VARCHAR(5) ,

@BeginDateTime VARCHAR(25) ,

@EndDateTime VARCHAR(25),

@DataParam VARCHAR(50)=NULL

AS

BEGIN

DECLARE @sql VARCHAR(5000)

SELECT @sql = ISNULL(@sql + ',', '') + '[' + PropertyText + ']'

FROM RPT_ReportProperty WITH ( NOLOCK )

WHERE ReportID = @ReportID

AND StatusFlag = 1

IF @DataParam IS NULL OR @DataParam=''

BEGIN

SET @sql = 'select ' + @sql

+ ' from(SELECT DataDate,PropertyText,DataValue FROM RPT_ReportProperty p WITH(NOLOCK) JOIN RPT_ReportData d WITH(NOLOCK) ON p.PropertyID=d.PropertyID WHERE d.ReportID='''

+ @ReportID + ''' AND DataDate>= ''' + @BeginDateTime

+ ''' AND DataDate< ''' + @EndDateTime

+ ''')tb pivot (max(DataValue) for PropertyText in (' + @sql

+ '))a'

END

ELSE

BEGIN

SET @sql = 'select ' + @sql

+ ' from(SELECT DataDate,PropertyText,DataValue FROM RPT_ReportProperty p WITH(NOLOCK) JOIN RPT_ReportData d WITH(NOLOCK) ON p.PropertyID=d.PropertyID WHERE d.ReportID='''

+ @ReportID + ''' AND DataDate>= ''' + @BeginDateTime

+ ''' AND DataDate< ''' + @EndDateTime

+ ''' AND DataParam= ''' + @DataParam

+ ''')tb pivot (max(DataValue) for PropertyText in (' + @sql

+ '))a'

END

EXEC(@sql)

END

GO

-------------------------------------------附加信息：-----------------------------------

SELECT * FROM RPT_Report --需要查询的数据

SELECT * FROM RPT_ReportScheduler ORDER BY CreateTime DESC --添加临时执行任务 循环表RPT_Report数据加到RPT_ReportScheduler

SELECT * FROM RPT_ReportProperty p WHERE PropertyText IN ( '月份','日期','入库单量','入库及时率','延误入库单量','异常入库单量' ) AND p.ReportID=3 --事先已经加入到统计查询语句

SELECT TOP 50 * FROM RPT_ReportData WHERE ReportID=3 ORDER BY CreateTime DESC --执行后的数据放在此表

在调度下面的：BusinessReportAnalysisScheduler

相关的表在百度云：TWX/定时统计报表的统计数据(各表).xls