sql server 2005 pivot 行列倒置请教
源表数据目标要求如下图所示是这样的：
以下为生成测试语句的SQL语句，偶先放在这儿了哈！
CREATE TABLE [dbo].[#CLOCK]
(
[dDate] [datetime] NOT NULL,
[TIME] [varchar](10) NOT NULL,
[ID] [varchar] (12) NOT NULL,
[Kind] [varchar] (10) NOT NULL
)
INSERT #CLOCK( dDate,TIME,ID,Kind)VALUES ( '2012-03-01','07:52:08','A02','上班入1' )
INSERT #CLOCK( dDate,TIME,ID,Kind)VALUES ( '2012-03-01','12:01:20','A02','上班出1' )
INSERT #CLOCK( dDate,TIME,ID,Kind)VALUES ( '2012-03-01','13:24:26','A02','上班入2' )
INSERT #CLOCK( dDate,TIME,ID,Kind)VALUES ( '2012-03-01','17:32:56','A02','上班出2' )
DROP TABLE #CLOCK
CREATE TABLE [dbo].[#CLOCK]
(
[dDate] [datetime] NOT NULL,
[TIME] [varchar](10) NOT NULL,
[ID] [varchar] (12) NOT NULL,
[Kind] [varchar] (10) NOT NULL
)
INSERT #CLOCK( dDate,TIME,ID,Kind)VALUES ( '2012-03-01','07:52:08','A02','上班入1' )
INSERT #CLOCK( dDate,TIME,ID,Kind)VALUES ( '2012-03-01','12:01:20','A02','上班出1' )
INSERT #CLOCK( dDate,TIME,ID,Kind)VALUES ( '2012-03-01','13:24:26','A02','上班入2' )
INSERT #CLOCK( dDate,TIME,ID,Kind)VALUES ( '2012-03-01','17:32:56','A02','上班出2' )
select
*
from
#CLOCK
pivot(max([TIME]) for [Kind]in([上班入1],[上班出1],[上班入2],[上班出2]))b
/*
dDate ID 上班入1 上班出1 上班入2 上班出2
----------------------- ------------ ---------- ---------- ---------- ----------
2012-03-01 00:00:00.000 A02 07:52:08 12:01:20 13:24:26 17:32:56
(1 行受影响)
*/
