问一个sql语句，解决马上结贴给分！（快，先到独得）

我有个一个表，结构是这样的：
CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Event1] [nvarchar](50) NULL,
[Event1At] [datetime] NULL,
[Event2] [nvarchar](50) NULL,
[Event2At] [datetime] NULL,
[Event3] [nvarchar](50) NULL,
[Event3At] [datetime] NULL
)

里面的数据这样的：

ID Event1 Event1At Event2 Event2At Event3 Event3At
--------------------------------------------------------------------------------------------------------
1 Login 2011-11-09 07:47:37.000 Attack 2011-11-09 07:47:52.000 Logout 2011-11-09 07:48:02.000
2 NULL NULL Attack 2011-11-10 15:23:05.000 NULL NULL


现在要一个select语句出来这样的记录：

ID Date Time Event1 Event2 Event3
---------------------------------------------------
1 2011-11-09 07:47:37 x
1 2011-11-09 07:47:52 x
1 2011-11-09 07:48:02 x
2 2011-11-10 15:23:05 x

我解释下：一条记录可能会SELECT出来多条（N<=3），如果记录中出现了Event1，那么就在Event1那个地方打个“x”，日期和时间列的值就是Event1的；如果记录中出现了Event2，那么就在Event2那个地方打个“x”，日期和时间列的值就是Event2的.... 如果没有就不要出现，所以一条记录会SELECT出来多条（N<=3）。这个应该不是很难。

排版乱了。

SQL code

select * 
from [TestTable] as a unpivot
(Value for Cols in([Event1At],[Event2At],[Event3At])) as  b

靠，csdn什么排版。最后那4个x不在一列上的啊。那4个x分别在1,2,3,2列上。对应Event1,2,3,Event2.

SQL code

CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Event1] [nvarchar](50) NULL,
[Event1At] [datetime] NULL,
[Event2] [nvarchar](50) NULL,
[Event2At] [datetime] NULL,
[Event3] [nvarchar](50) NULL,
[Event3At] [datetime] NULL
)
insert into testtable select 'Login','2011-11-09 07:47:37.000','Attack','2011-11-09 07:47:52.000','Logout','2011-11-09 07:48:02.000'
insert into testtable select NULL,NULL,'Attack','2011-11-10 15:23:05.000',NULL,NULL
go
select id,event1at as dt,'x' as event1,null as event2,null as event3 from testtable where event1 is not null
union all
select id,event1at as dt,null,'x',null from testtable where event2 is not null
union all
select id,event1at as dt,null,null,'x' from testtable where event3 is not null
order by id
/*
id          dt                      event1 event2 event3
----------- ----------------------- ------ ------ ------
1           2011-11-09 07:47:37.000 x      NULL   NULL
1           2011-11-09 07:47:37.000 NULL   x      NULL
1           2011-11-09 07:47:37.000 NULL   NULL   x
2           NULL                    NULL   x      NULL

(4 行受影响)

*/
go
drop table testtable

SQL code

CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Event1] [nvarchar](50) NULL,
[Event1At] [datetime] NULL,
[Event2] [nvarchar](50) NULL,
[Event2At] [datetime] NULL,
[Event3] [nvarchar](50) NULL,
[Event3At] [datetime] NULL
)
insert into testtable select 'Login','2011-11-09 07:47:37.000','Attack','2011-11-09 07:47:52.000','Logout','2011-11-09 07:48:02.000'
insert into testtable select NULL,NULL,'Attack','2011-11-10 15:23:05.000',NULL,NULL
go
select id,event1at as dt,'x' as event1,null as event2,null as event3 from testtable where event1 is not null
union all
select id,event2at as dt,null,'x',null from testtable where event2 is not null
union all
select id,event3at as dt,null,null,'x' from testtable where event3 is not null
order by id
/*
id          dt                      event1 event2 event3
----------- ----------------------- ------ ------ ------
1           2011-11-09 07:47:37.000 x      NULL   NULL
1           2011-11-09 07:47:52.000 NULL   x      NULL
1           2011-11-09 07:48:02.000 NULL   NULL   x
2           2011-11-10 15:23:05.000 NULL   x      NULL

(4 行受影响)

*/
go
drop table testtable