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

 

转载于:https://www.cnblogs.com/chengjun/p/9318279.html