表tb
id cardtime hours
1 2014-05-01 08:30
1 2014-05-01 18:30
2 2014-05-02 08:00
2 2014-05-02 13:00
2 2014-05-02 18:00
......
想转化成同一个ID，同一天，都转化在同一列上 如
1 2014-05-01 08:30 18:30
2 2014-05-02 08:00 13:00 18:00
select [idcard],[cardtime] from tb group by [idcard],[cardtime] PIVOT hours
这段代码运行错误 ，我是参考这个写的http://bbs.csdn.net/topics/210069998 请 大侠告知 小弟谢了
------解决方案--------------------
在同一列的话应该不是用Pivot
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-12 11:36:02
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[cardtime] date,[hours] time)
insert [tb]
select 1,'2014-05-01', '08:30' union all
select 1,'2014-05-01', '18:30' union all
select 2,'2014-05-02', '08:00' union all
select 2,'2014-05-02', '13:00' union all
select 2,'2014-05-02', '18:00'
--------------开始查询--------------------------
--select * from [tb]
select a.[id],a.[cardtime],
stuff((select ' '+CAST([hours] AS VARCHAR(5)) from [tb] b
where b.[id]=a.[id] and b.[cardtime]=a.[cardtime]
for xml path('')),1,1,'') [hours]
from [tb] a
group by a.[id],a.[cardtime]
--select [idcard],[cardtime] from tb PIVOT (MAX([hours] FOR [hours] IN ())
----------------结果----------------------------
/*
id cardtime hours
----------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 2014-05-01 08:30 18:30
2 2014-05-02 08:00 13:00 18:00
*/
------解决方案--------------------
这样？code=sql]----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-12 11:36:02
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[cardtime] date,[hours] time)
insert [tb]
select 1,'2014-05-01', '08:30' union all
select 1,'2014-05-01', '18:30' union all
select 2,'2014-05-02', '08:00' union all
select 2,'2014-05-02', '13:00' union all
select 2,'2014-05-02', '18:00'
--------------开始查询--------------------------
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([hours]) from tb group by [hours]
exec ('select * from tb pivot (max([hours]) for [hours] in('+@s+'))b')
----------------结果----------------------------
/*
id cardtime 08:00:00.0000000 08:30:00.0000000 13:00:00.0000000 18:00:00.0000000 18:30:00.0000000
----------- ---------- ---------------- ---------------- ---------------- ---------------- ----------------
1 2014-05-01 NULL 08:30:00.0000000 NULL NULL 18:30:00.0000000