笔记219 行转列 不用pivot,只能有一行记录 转换后再进行处理2012-11-15
笔记219 行转列 不用pivot,只能有一行记录 转换后再进行处理2012-11-15
1 --行转列 不用pivot,只能有一行记录 转换后再进行处理2012-11-15 2 --http://social.technet.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/37448887-2349-49f7-9709-53b65389a620 3 --建立测试表和测试数据 4 USE [pratice] 5 CREATE TABLE RowToColumn 6 ( 7 Col1 DATETIME , 8 Col2 DATETIME , 9 Col3 DATETIME , 10 Col4 DATETIME , 11 Col5 DATETIME , 12 Col6 DATETIME , 13 Col7 DATETIME , 14 Col8 DATETIME 15 ) 16 17 INSERT RowToColumn 18 SELECT '2012-01-01','2012-01-28','2012-01-29','2012-02-25','2012-02-26','2012-03-31','2012-04-01','2012-04-28' 19 SELECT * FROM [dbo].[RowToColumn] 20 21 ------------------------------------------------------------------------------------ 22 --开始循环 将行转换为列 23 24 USE [pratice] 25 DECLARE @sql NVARCHAR(100) 26 CREATE TABLE #test(Col DATETIME) 27 DECLARE @name NVARCHAR(10) 28 DECLARE Employee_Cursor CURSOR FOR select name from sys.columns WHERE object_id = object_id('RowToColumn') 29 OPEN Employee_Cursor 30 FETCH NEXT FROM Employee_Cursor into @name 31 WHILE @@FETCH_STATUS = 0 32 BEGIN 33 SET @sql='SELECT '+@name+' FROM [pratice].[dbo].[RowToColumn]' 34 EXEC(@sql) 35 insert INTO #test EXEC(@sql) 36 FETCH NEXT FROM Employee_Cursor into @name 37 END 38 CLOSE Employee_Cursor 39 DEALLOCATE Employee_Cursor 40 SELECT * FROM #test 41 ----------------------------------------------------------------------------------------- 42 --sql语句并接出结果 43 if not object_id('Tempdb..#C') is null 44 drop table #C 45 Go 46 CREATE TABLE #C(period INT,Col DATETIME) 47 GO 48 INSERT #C SELECT MONTH(Col) ,Col FROM #test 49 GO 50 --SELECT * FROM [#C] 51 --GO 52 53 DECLARE @d TABLE(period INT,Col DATETIME) 54 INSERT @d SELECT * FROM #C AS aa WHERE [Col]=(SELECT MIN(Col) FROM #C WHERE aa.[period]=[period]) ORDER BY [aa].[period] 55 --SELECT * FROM @d 56 57 58 DECLARE @e TABLE(period INT,Col DATETIME) 59 INSERT @e SELECT * FROM #C AS aa WHERE [Col]=(SELECT MAX([Col]) FROM #C WHERE aa.[period]=[period]) ORDER BY [aa].[period] 60 --SELECT * FROM @e 61 62 SELECT a.[period] AS Period,a.[Col] AS BeginDate,b.[Col] AS ENDDate FROM @d AS a INNER JOIN @e AS b ON b.[period]=[a].[period] 63 64 --------------------------钟进的方法---------------------------------------------------------------------------- 65 USE [pratice] 66 67 IF NOT OBJECT_ID ('Tempdb..#D') IS NULL 68 DROP TABLE #D 69 GO 70 71 CREATE TABLE #D 72 ( 73 [period] [int] IDENTITY(1,1) NOT NULL, 74 [BeginDate] DATETIME NULL, 75 [EndDate] DATETIME NULL 76 ) 77 DECLARE @maxCol INT 78 DECLARE @tem INT 79 DECLARE @colName NVARCHAR(10) 80 DECLARE @colName2 NVARCHAR(10) 81 DECLARE @SQL NVARCHAR(1000) 82 SELECT @tem=1,@maxCol=COUNT(1) FROM sys.columns 83 WHERE object_id = OBJECT_ID ('RowToColumn') 84 85 86 WHILE (@tem<=@maxCol) 87 BEGIN 88 SELECT @colName=[name],@colName2='' 89 FROM sys.columns 90 WHERE object_id = OBJECT_ID ('RowToColumn') AND [column_id]=@tem 91 92 SELECT @colName2=[name] 93 FROM sys.columns 94 WHERE object_id = OBJECT_ID ('RowToColumn') AND [column_id]=@tem+1 95 96 IF (ISNULL(@colName2,'')='') 97 SELECT @SQL='INSERT INTO #D(BeginDate,EndDate) SELECT '+@colName+',NULL FROM RowToColumn' 98 ELSE 99 SELECT @SQL='INSERT INTO #D(BeginDate,EndDate) SELECT '+@colName+','+@colName2+' FROM RowToColumn' 100 101 EXEC(@SQL) 102 SET @tem=@tem+2 103 END 104 105 SELECT * FROM #D 106 107 IF NOT OBJECT_ID ('Tempdb..#D') IS NULL 108 DROP TABLE #D