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