T-sql 行转列，数据库查询分页
2024-09-17 13:30:47
1 USE [APS_Future_FT]
2 GO
3 /****** Object: StoredProcedure [dbo].[A_CrudePrice] Script Date: 2013/11/5 19:13:21 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 -- =============================================
9 -- Author: <Author,,Name>
10 -- Create date: <Create Date,,>
11 -- Description: <Description,,>
12 -- =============================================
13 ALTER PROCEDURE [dbo].[A_CrudePrice]
14 (
15 @BeginDate DATETIME ,
16 @EndDate DATETIME,
17 @PageSize INT,
18 @PageIndex INT,
19 @TotalCount INT OUTPUT
20 )
21 AS
22 BEGIN
23 -- SET NOCOUNT ON added to prevent extra result sets from
24 -- interfering with SELECT statements.
25 SET NOCOUNT ON;
26
27 DECLARE @CodeList NVARCHAR(max)
28 DECLARE @str NVARCHAR(max)
29 SET @CodeList =( SELECT DISTINCT '['+ iscp.CrudeStandard+'],'
30 FROM dbo.T_H_InternalStandardCrudePrice iscp
31 FOR XML PATH('')
32 )
33 SET @CodeList = SUBSTRING(@CodeList,1,LEN(@CodeList)-1)
34 PRINT @CodeList
35
36 SELECT iscp.PriceDate,iscp.CrudeStandard,iscp.CrudeStandardPrice
37 INTO #A
38 FROM dbo.T_H_InternalStandardCrudePrice iscp
39 WHERE iscp.PriceDate BETWEEN @BeginDate AND @EndDate
40
41
42 SET @str = '
43 SELECT Row=row_number() over(order by PriceDate desc), PriceDate,'+@CodeList+'
44 FROM (
45 select * from #A
46 )AS A
47 PIVOT ( sum(A.CrudeStandardPrice) FOR A.CrudeStandard IN ('+@CodeList+' )
48 )AS B'
49 SET @str =' SELECT * INTO ##A
50 FROM ('+@str+') as C'
51
52 EXEC(@str)
53
54 SELECT *
55 FROM ##A AS tempA
56 WHERE tempA.Row BETWEEN @PageSize * @PageIndex AND @PageSize *(@PageIndex + 1)
57
58 SELECT @TotalCount =( SELECT COUNT(*)
59 FROM ##A
60 )
61 DROP TABLE #A
62 DROP TABLE ##A
63
64 END
最新文章
- LINQ系列：LINQ to XML查询
- 通过SQL Server自定义数据类型实现导入数据
- MBTI-性格测试
- thinkphp中volist标签
- Google Protocol Buffer 的使用
- Win7配置Go环境
- 14.Android之Layout布局学习
- java获取客户访问IP
- [Wordpress]Wordpress使用SMTP发送电邮
- 基于RSA securID的Radius二次验证java实现(PAP验证方式)
- jquery遍历二维数组
- win10 uwp 通知Toast
- SAP Brazil J1BTAX 为税收例外创建税收组（翻译）
- ML: 降维算法-LE
- SPSS-聚类分析
- node.js second day
- CF 554A 字符串水题
- GridEh Lookup
- HDU 1213（裸并查集）（无变形）
- Echarts-K线图提示框改头换面