行专列之后，查询的存储过程为
1 USE [APS_Future_FT] 2 GO 3 /****** Object: StoredProcedure [dbo].[P_APS_H_InternalStandardCrudePrice] Script Date: 2013/11/7 21:27:23 ******/ 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].[P_APS_H_InternalStandardCrudePrice] 14 ( 15 @PageSize INT , 16 @PageIndex INT , 17 @BeginDate DATETIME , 18 @EndDate DATETIME , 19 @TotalCount INT OUTPUT 20 ) 21 AS 22 BEGIN 23 24 SET NOCOUNT ON; 25 --------------------------------------------------------- 26 SET @TotalCount=0 27 28 SELECT thi.CrudeStandardPriceID , 29 vab.Name , 30 vab.Code , 31 thi.CrudeStandardPrice , 32 thi.CreateDate , 33 CONVERT(VARCHAR(10), PriceDate, 120) AS PriceDate 34 INTO #A 35 FROM T_H_InternalStandardCrudePrice thi , 36 dbo.V_APS_BaseDataUnion vab 37 WHERE thi.CrudeStandard = vab.Code 38 AND thi.PriceDate BETWEEN @BeginDate AND @EndDate 39 ORDER BY thi.PriceDate ASC 40 ---------------------------------------------------------- 41 DECLARE @i INT 42 SET @i = 0 43 44 SELECT @i = ( SELECT COUNT(*) 45 FROM #A 46 ) 47 IF ( @i > 0 ) 48 BEGIN 49 --读取动态列 50 DECLARE @STR NVARCHAR(MAX); 51 SELECT @STR = ( SELECT DISTINCT 52 '[' + Code + '],' 53 FROM #A 54 FOR 55 XML PATH('') 56 ) 57 SELECT @STR = SUBSTRING(@STR, 1, LEN(@STR) - 1) 58 59 DECLARE @ESTR NVARCHAR(MAX) 60 61 ------------------------------------------------------------- 62 --行转列 63 SET @ESTR = 'select rn=row_number()over (order by PriceDate desc),PriceDate,' 64 + @STR + ' 65 FROM ( SELECT 66 Code , 67 CrudeStandardPrice , 68 PriceDate 69 FROM #A 70 ) AS A PIVOT ( SUM(A.CrudeStandardPrice) FOR A.Code IN (' 71 + @STR + ' ) ) AS B ' 72 73 -- SET @ESTR = 'select * 74 -- FROM ( SELECT 75 -- Code , 76 -- CrudeStandardPrice , 77 --PriceDate 78 -- FROM #A 79 -- ) AS A PIVOT ( SUM(A.CrudeStandardPrice) FOR A.Code IN (' 80 -- + @STR + ' ) ) AS B' 81 --------------------------------------------------------------- 82 --查询总记录数 83 84 DECLARE @sql NVARCHAR(MAX) 85 SET @sql = 'select * into ##C from (' + @ESTR + ') as C' 86 87 EXEC(@sql) 88 89 SELECT @TotalCount = ( SELECT COUNT(*) 90 FROM ##C 91 ) 92 DROP TABLE ##C 93 ---------------------------------------------------------------- 94 --增加分页 95 SET @ESTR = 'select * from (' + @ESTR 96 + ') as C where rn BETWEEN ' 97 + CAST(( ( @PageIndex ) * @PageSize + 1 ) AS NVARCHAR(20)) 98 + ' and ' 99 + CAST(( ( @PageIndex + 1 ) * @PageSize ) AS NVARCHAR(20)) 100 + ' ' 101 102 EXEC (@ESTR) 103 ------------------------------------------------------------- 104 END 105 DROP TABLE #A 106 107 END
xml文件为
<data><row id="14" code="93_06" value="100" state="added" rowid="1" /><row id="14" code="name" value="New Row" state="added" rowid="2" /><row id="14" code="_uid" value="14" state="added" rowid="3" /><row id="14" code="93_11" value="100" state="added" rowid="4" /><row id="14" code="93_10" value="100" state="added" rowid="5" /><row id="14" code="93_09" value="100" state="added" rowid="6" /><row id="14" code="93_08" value="100" state="added" rowid="7" /><row id="14" code="93_12" value="100" state="added" rowid="8" /><row id="14" code="93_03" value="100" state="added" rowid="9" /><row id="14" code="93_02" value="210" state="added" rowid="10" /><row id="14" code="93_01" value="120" state="added" rowid="11" /><row id="14" code="93_07" value="100" state="added" rowid="12" /><row id="14" code="PriceDate" value="2013/11/1 0:00:00" state="added" rowid="13" /><row id="14" code="_id" value="14" state="added" rowid="14" /><row id="14" code="93_04" value="100" state="added" rowid="15" /><row id="14" code="93_05" value="100" state="added" rowid="16" /><row id="14" code="_state" value="added" state="added" rowid="17" /><row id="11" code="rn" value="1" state="modified" rowid="1" /><row id="11" code="_uid" value="11" state="modified" rowid="2" /><row id="11" code="93_12" value="200" state="modified" rowid="3" /><row id="11" code="93_11" value="200" state="modified" rowid="4" /><row id="11" code="93_10" value="200" state="modified" rowid="5" /><row id="11" code="93_06" value="200" state="modified" rowid="6" /><row id="11" code="93_09" value="200" state="modified" rowid="7" /><row id="11" code="93_08" value="200" state="modified" rowid="8" /><row id="11" code="93_03" value="200" state="modified" rowid="9" /><row id="11" code="93_02" value="200" state="modified" rowid="10" /><row id="11" code="93_01" value="200" state="modified" rowid="11" /><row id="11" code="93_07" value="200" state="modified" rowid="12" /><row id="11" code="PriceDate" value="2013-10-28" state="modified" rowid="13" /><row id="11" code="93_05" value="200" state="modified" rowid="14" /><row id="11" code="93_04" value="200" state="modified" rowid="15" /><row id="11" code="_id" value="11" state="modified" rowid="16" /><row id="11" code="_state" value="modified" state="modified" rowid="17" /></data>
存储过程为
1 USE [APS_Future_FT] 2 GO 3 /****** Object: StoredProcedure [dbo].[A_001] Script Date: 2013/11/7 19:23:03 ******/ 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_001] ( @str XML ) 14 AS 15 BEGIN 16 -- SET NOCOUNT ON added to prevent extra result sets from 17 -- interfering with SELECT statements. 18 SET NOCOUNT ON; 19 20 DECLARE @T TABLE 21 ( 22 T_id NVARCHAR(50) , 23 T_code NVARCHAR(50) , 24 T_value NVARCHAR(50) , 25 T_state NVARCHAR(50) 26 ) 27 28 INSERT INTO @T 29 ( T_id , 30 T_code , 31 T_value , 32 T_state 33 ) 34 SELECT T.r.value('@id', 'nvarchar(50)') , 35 T.r.value('@code', 'nvarchar(50)'), 36 T.r.value('@value','nvarchar(50)'), 37 T.r.value('@state','nvarchar(50)') 38 FROM @str.nodes('/data/row') T ( r ) 39 40 41 42 SELECT DISTINCT * 43 INTO #Date 44 FROM @T 45 WHERE T_code = 'PriceDate' 46 47 DELETE FROM @T 48 WHERE T_code LIKE '"_%' ESCAPE '"' 49 OR T_code = 'rn' 50 OR T_code = 'PriceDate' 51 52 SELECT T.*, 53 d.T_value T_date 54 INTO #ALL 55 FROM @T AS T 56 INNER JOIN #Date d ON T.T_id = d.T_id 57 58 --------------------------删除 59 DELETE iscp 60 FROM dbo.T_H_InternalStandardCrudePrice iscp 61 INNER JOIN #ALL a ON a.T_date = iscp.PriceDate 62 AND a.T_code = iscp.CrudeStandard 63 AND a.T_state = 'removed' 64 65 66 ---------------------------修改 67 SELECT iscp.CrudeStandardPriceID, 68 a.* 69 INTO #UPDATE 70 FROM #ALL a 71 INNER JOIN dbo.T_H_InternalStandardCrudePrice iscp 72 ON a.T_date = iscp.PriceDate 73 AND a.T_code = iscp.CrudeStandard 74 75 --SELECT * FROM #UPDATE 76 77 UPDATE T_H_InternalStandardCrudePrice 78 SET CrudeStandardPrice = up.T_value 79 FROM dbo.T_H_InternalStandardCrudePrice iscp 80 INNER JOIN #UPDATE up ON iscp.CrudeStandardPriceID = up.CrudeStandardPriceID 81 82 --------------------------------增加 83 84 INSERT INTO dbo.T_H_InternalStandardCrudePrice 85 ( CrudeStandard , 86 PriceDate , 87 CrudeStandardPrice 88 ) 89 SELECT A.T_code, 90 CASE WHEN A.T_date IS NOT NULL 91 THEN CAST(A.T_date AS DATETIME) 92 ELSE NULL 93 END 94 , 95 CASE WHEN A.T_value IS NOT NULL 96 THEN CAST(A.T_value AS DECIMAL) 97 ELSE NULL 98 END 99 100 FROM ( 101 SELECT a.T_id, 102 a.T_code, 103 a.T_value, 104 a.T_state, 105 a.T_date 106 FROM #ALL a 107 EXCEPT 108 SELECT up.T_id, 109 up.T_code, 110 up.T_value, 111 up.T_state, 112 up.T_date 113 FROM #UPDATE up 114 115 )AS A 116 117 END