积极答复者
【求助】求SQL代码转换表结构

问题
-
表1 : 只有一列
date
2011-1-1
2011-2-1
2011-3-1
2011-4-1
2011-5-1
2011-6-1
表2
item
start
end
A
2011-2-1
2011-3-16
B
2011-1-1
2011-4-20
C
2011-3-10
2011-6-1
D
2011-4-15
2011-5-1
求SQL代码(MS SQL )
将上述两表转换成以下的表
表3
item
2011-1-1
2011-2-1
2011-3-1
2011-4-1
2011-5-1
20116-1
A
Y
Y
B
Y
Y
Y
Y
C
Y
Y
Y
Y
D
Y
Y
答案
-
你好,
如果把‘Y’改成数字的话,就可以用 PIOVT 写出来了。
declare @table1 table (Date date) insert @table1 values('2011-1-1') insert @table1 values('2011-2-1') insert @table1 values('2011-3-1') insert @table1 values('2011-4-1') insert @table1 values('2011-5-1') insert @table1 values('2011-6-1') declare @table2 table( item varchar(2), starttime date, Endtime date) insert @table2 values('A','2011-2-1','2011-3-16') insert @table2 values('B','2011-1-1','2011-4-20') insert @table2 values('C','2011-3-10','2011-6-1') insert @table2 values('D','2011-4-15','2011-5-1') SELECT item ,[2011-1-1], [2011-2-1], [2011-3-1], [2011-4-1], [2011-5-1],[2011-6-1] FROM (SELECT item ,Date, starttime,endtime FROM @table2 join @table1 on date between starttime and Endtime) AS SourceTable PIVOT ( count(Date) FOR Date IN ([2011-1-1], [2011-2-1], [2011-3-1], [2011-4-1], [2011-5-1],[2011-6-1]) ) AS PivotTable;
thanks.
Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.- 已标记为答案 Molly Chen_Moderator 2011年11月10日 5:26
全部回复
-
或許PIVOT能夠符合你的需求。
http://msdn.microsoft.com/zh-cn/library/ms177410(v=SQL.105).aspx
PS:必須是SQL Server 2005以上。
以上說明若有錯誤請指教,謝謝。
http://www.dotblogs.com.tw/terrychuang/ -
你好,
如果把‘Y’改成数字的话,就可以用 PIOVT 写出来了。
declare @table1 table (Date date) insert @table1 values('2011-1-1') insert @table1 values('2011-2-1') insert @table1 values('2011-3-1') insert @table1 values('2011-4-1') insert @table1 values('2011-5-1') insert @table1 values('2011-6-1') declare @table2 table( item varchar(2), starttime date, Endtime date) insert @table2 values('A','2011-2-1','2011-3-16') insert @table2 values('B','2011-1-1','2011-4-20') insert @table2 values('C','2011-3-10','2011-6-1') insert @table2 values('D','2011-4-15','2011-5-1') SELECT item ,[2011-1-1], [2011-2-1], [2011-3-1], [2011-4-1], [2011-5-1],[2011-6-1] FROM (SELECT item ,Date, starttime,endtime FROM @table2 join @table1 on date between starttime and Endtime) AS SourceTable PIVOT ( count(Date) FOR Date IN ([2011-1-1], [2011-2-1], [2011-3-1], [2011-4-1], [2011-5-1],[2011-6-1]) ) AS PivotTable;
thanks.
Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.- 已标记为答案 Molly Chen_Moderator 2011年11月10日 5:26