笔记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

 

posted @ 2013-08-04 13:43  桦仔  阅读(573)  评论(0)    收藏  举报