行转列，但不使用聚合
本帖最后由 blue_morning 于 2013-06-04 20:04:26 编辑 pivot 做行转列时，要求一个聚合函数，但我不想聚合只是行转列。下面是我已经准备好的SQL：
CREATE TABLE [dbo].[ZTest](
[ID] [uniqueidentifier] NOT NULL ,
[WatchObjID] [int] NOT NULL,
[SensorID] [int] NOT NULL,
[CollectDateTime] [datetime] NOT NULL,
[WatchValue] [numeric](20, 4) NULL
) ON [PRIMARY]
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{01454A37-5E65-4929-BAF7-60BE6D588515}', 5, 1, '2013-06-03 14:59:42', 4.1100
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{02FBB8E8-CDC2-4EAB-8866-70D9FF6D42A3}', 5, 2, '2013-06-03 14:59:42', 4.2200
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{DDCF40D7-05CA-456E-8980-BFE28E801499}', 5, 3, '2013-06-03 14:59:42', 4.3300
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{F38006A6-8934-49C1-BB6C-BEF9CAA3A25E}', 5, 4, '2013-06-03 14:59:42', 4.4400
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{B87E08A0-BF71-485F-84F9-EA41FBDC1314}', 5, 5, '2013-06-03 14:59:42', 4.5500
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{93AFCF38-B7A9-4711-9BA2-CB28354D6310}', 5, 1, '2013-06-03 14:59:45', 3.1000
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{EBE9BAD1-381B-4916-AA05-30681942361D}', 5, 2, '2013-06-03 14:59:45', 3.2000
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{1C660463-2D1F-45A7-A4F9-60870E2EACC3}', 5, 3, '2013-06-03 14:59:45', 3.3000
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{CA71B2EF-1CE6-4683-8904-72DA67A890B0}', 5, 4, '2013-06-03 14:59:45', 3.4000
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{FEFB320E-F37C-4EE4-8D6A-E770EEEC8E79}', 5, 5, '2013-06-03 14:59:45', 3.5000
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{3F8D0DD8-6308-4F7D-97E4-BE6936C29E0F}', 3, 1, '2013-06-03 15:00:05', 1.1000
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{06AD768D-7804-4E07-9DB6-F83E828F6074}', 3, 2, '2013-06-03 15:00:05', 1.2000
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{ECA74E2A-AB09-40A4-9E2A-6579F2C60231}', 3, 3, '2013-06-03 15:00:05', 1.3000
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{364C2476-124D-49B5-B090-58C9AEC190E0}', 3, 4, '2013-06-03 15:00:05', 1.4000
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{9A221E39-4558-456E-899D-52D0444C6283}', 3, 5, '2013-06-03 15:00:05', 1.5000
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{A8F09D0A-0249-438C-AF2D-7437773C8A2E}', 3, 1, '2013-06-03 14:59:56', 2.1000
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{2EE2B9E8-0318-4E1B-9A9C-7BADD952A299}', 3, 2, '2013-06-03 14:59:56', 2.2000
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{FF4F26A8-C482-48C1-80CE-1EEA224E544D}', 3, 3, '2013-06-03 14:59:56', 2.3000
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{57BC227A-1F3F-48BC-A23C-CE4CA49EA496}', 3, 4, '2013-06-03 14:59:56', 2.4000
)
insert into ZTest(
ID, WatchObjID, SensorID, CollectDateTime, WatchValue
)
values(
'{22670494-E0C9-4001-BC87-31950F303E54}', 3, 5, '2013-06-03 14:59:56', 2.5000
)
select * from zTest
drop table zTest
[解决办法]
try this,
select WatchObjID,CollectDateTime,
[1] 'SensorID_1',
[2] 'SensorID_2',
[3] 'SensorID_3',
[4] 'SensorID_4',
[5] 'SensorID_5'
from (select WatchObjID,CollectDateTime,SensorID,WatchValue from zTest) a
pivot(max(WatchValue) for SensorID in([1],[2],[3],[4],[5])) p
/*
WatchObjID CollectDateTime SensorID_1 SensorID_2 SensorID_3 SensorID_4 SensorID_5
----------- ----------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
5 2013-06-03 14:59:42.000 4.1100 4.2200 4.3300 4.4400 4.5500
5 2013-06-03 14:59:45.000 3.1000 3.2000 3.3000 3.4000 3.5000
3 2013-06-03 14:59:56.000 2.1000 2.2000 2.3000 2.4000 2.5000
3 2013-06-03 15:00:05.000 1.1000 1.2000 1.3000 1.4000 1.5000
(4 row(s) affected)
*/
