表的select数据结果如下:
ID DUTY_DATE TIME FLAG
18801155 2011-11-2 07:58:32 入
18801155 2011-11-2 10:03:16 出
18801155 2011-11-2 12:43:30 入
18801155 2011-11-2 16:13:18 入
18801155 2011-11-2 16:11:02 出
18801155 2011-11-2 16:30:02 出
18801155 2011-11-2 17:13:42 入能不能改为: ID DUTY_DATE FLAG TIME1 TIME2 TIME3
18801155 2011-11-2 入 07:58:32 12:43:30 17:13:42
18801155 2011-11-2 出 10:03:16 16:11:02 16:30:02 请指教
ID DUTY_DATE TIME FLAG
18801155 2011-11-2 07:58:32 入
18801155 2011-11-2 10:03:16 出
18801155 2011-11-2 12:43:30 入
18801155 2011-11-2 16:13:18 入
18801155 2011-11-2 16:11:02 出
18801155 2011-11-2 16:30:02 出
18801155 2011-11-2 17:13:42 入能不能改为: ID DUTY_DATE FLAG TIME1 TIME2 TIME3
18801155 2011-11-2 入 07:58:32 12:43:30 17:13:42
18801155 2011-11-2 出 10:03:16 16:11:02 16:30:02 请指教
18801155 2011-11-2 入 07:58:32 12:43:30 17:13:42
18801155 2011-11-2 出 10:03:16 16:11:02 16:30:02
-->>行列转换,但是问题在于,你的入有四条,那么是只显示3条,还是都要显示?
id,
DUTY_DATE,flag
max(case px when 1 then TIME else '' end) as time1,
max(case px when 2 then TIME else '' end) as time2,
max(case px when 3 then TIME else '' end) as time3
from
(select px=row_number()over(partition by id order by getdate()),* from tb)t
set nocount on
if OBJECT_ID('A','u') is not null drop table A
go
create table A
(
a nvarchar(10),
b datetime,
c nvarchar(5)
)
go
insert into A
select '18801155' as a, '2011-11-2 07:58:32' as b, '入' as c union all
select '18801155' as a, '2011-11-2 10:03:16' as b, '出' as c union all
select '18801155' as a, '2011-11-2 12:43:30' as b,'入' as c union all
select '18801155' as a, '2011-11-2 16:13:18' as b, '入' as c union all
select '18801155' as a, '2011-11-2 16:11:02' as b, '出' as c union all
select '18801155' as a, '2011-11-2 16:30:02' as b, '出' as c union all
select '18801155' as a, '2011-11-2 17:13:42' as b, '入' as cgoselect distinct A,cast(B as DATE) as b,c,d
from
(
select *,
stuff(
(select +' '+ltrim(DATEPART(HH,B))+':'+ltrim(DATEPART(mi,B))+':'+ltrim(DATEPART(ss,B))
from A as b where a.c=b.c for XML path('')
),1,2,'') as d
from A as a
) as b
/*
A b c d
---------- ---------- ----- --------------------------------------
18801155 2011-11-02 出 10:3:16 16:11:2 16:30:2
18801155 2011-11-02 入 7:58:32 12:43:30 16:13:18 17:13:42
*/
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[DUTY_DATE] Datetime,[TIME] Datetime,[FLAG] nvarchar(1))
Insert #T
select 18801155,'2011-11-2','07:58:32',N'入' union all
select 18801155,'2011-11-2','10:03:16',N'出' union all
select 18801155,'2011-11-2','12:43:30',N'入' union all
select 18801155,'2011-11-2','16:13:18',N'入' union all
select 18801155,'2011-11-2','16:11:02',N'出' union all
select 18801155,'2011-11-2','16:30:02',N'出' union all
select 18801155,'2011-11-2','17:13:42',N'入'
GoDECLARE @s NVARCHAR(4000),@i NVARCHAR(2)Select TOP 1 @i=COUNT(*),@s='' from #T GROUP BY [DUTY_DATE],[ID],[FLAG]WHILE @i>0
SELECT @s=',[Time'+@i+']=max(case when row='+@i+' then [TIME] end)'+@s,@i=@i-1
EXEC('SELECT [ID],[DUTY_DATE],[FLAG]'+@s+'
FROM
(SELECT *,row=(SELECT COUNT(1) FROM #T WHERE [DUTY_DATE]=a.[DUTY_DATE] AND [ID]=a.[ID] AND [FLAG]=a.[FLAG] AND [TIME]<=a.[TIME])
FROM #T AS a
)t
GROUP BY [ID],[DUTY_DATE],[FLAG]')/*
ID DUTY_DATE FLAG Time1 Time2 Time3 Time4
18801155 2011-11-02 00:00:00.000 入 1900-01-01 07:58:32.000 1900-01-01 12:43:30.000 1900-01-01 16:13:18.000 1900-01-01 17:13:42.000
18801155 2011-11-02 00:00:00.000 出 1900-01-01 10:03:16.000 1900-01-01 16:11:02.000 1900-01-01 16:30:02.000 NULL
*/
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[DUTY_DATE] Datetime,[TIME] NVARCHAR(8),[FLAG] nvarchar(1))
Insert #T
select 18801155,'2011-11-2','07:58:32',N'入' union all
select 18801155,'2011-11-2','10:03:16',N'出' union all
select 18801155,'2011-11-2','12:43:30',N'入' union all
select 18801155,'2011-11-2','16:13:18',N'入' union all
select 18801155,'2011-11-2','16:11:02',N'出' union all
select 18801155,'2011-11-2','16:30:02',N'出' union all
select 18801155,'2011-11-2','17:13:42',N'入'
GoDECLARE @s NVARCHAR(4000),@i NVARCHAR(2)Select TOP 1 @i=COUNT(*),@s='' from #T GROUP BY [DUTY_DATE],[ID],[FLAG]WHILE @i>0
SELECT @s=',[Time'+@i+']=max(case when row='+@i+' then [TIME] end)'+@s,@i=@i-1
EXEC('SELECT [ID],[DUTY_DATE],[FLAG]'+@s+'
FROM
(SELECT *,row=(SELECT COUNT(1) FROM #T WHERE [DUTY_DATE]=a.[DUTY_DATE] AND [ID]=a.[ID] AND [FLAG]=a.[FLAG] AND [TIME]<=a.[TIME])
FROM #T AS a
)t
GROUP BY [ID],[DUTY_DATE],[FLAG]')/*
ID DUTY_DATE FLAG Time1 Time2 Time3 Time4
18801155 2011-11-02 00:00:00.000 入 07:58:32 12:43:30 16:13:18 17:13:42
18801155 2011-11-02 00:00:00.000 出 10:03:16 16:11:02 16:30:02 NULL*/
全部显示,
是可以直接SQL 实现还是要自己写函数或者过程实现?
create table tb(ID int, DUTY_DATE char(10),[TIME] char(8),FLAG varchar(5))
insert into tb
select 18801155,'2011-11-2','07:58:32','入' union all
select 18801155,'2011-11-2','10:03:16','出' union all
select 18801155,'2011-11-2','12:43:30','入' union all
select 18801155,'2011-11-2','16:13:18','入' union all
select 18801155,'2011-11-2','16:11:02','出' union all
select 18801155,'2011-11-2','16:30:02','出' union all
select 18801155,'2011-11-2','17:13:42','入' union all
select 18801156,'2011-11-2','07:58:32','入' union all
select 18801156,'2011-11-2','10:03:16','出' union all
select 18801156,'2011-11-2','12:43:30','入' union all
select 18801157,'2011-11-2','16:13:18','入' union all
select 18801157,'2011-11-2','16:11:02','出'DECLARE @SQL VARCHAR(8000),@SQL1 VARCHAR(8000)
SELECT @SQL = ISNULL(@SQL + '],[' , '') + b.sj FROM (select 'Time'+LTRIM(a.row) as sj,a.ID,a.DUTY_DATE,a.[TIME],a.FLAG from (
select ROW_NUMBER()over(partition by id,duty_date,flag order by [time])as row,ID, DUTY_DATE,[TIME],FLAG from tb) as a ) as b GROUP BY b.sj
SET @SQL = '[' + @SQL + ']'
exec(' SELECT *
FROM (
SELECT *
FROM (select ''Time''+LTRIM(a.row) as sj,a.ID,a.DUTY_DATE,a.[TIME],a.FLAG from (
select ROW_NUMBER()over(partition by id,duty_date,flag order by [time])as row,ID, DUTY_DATE,[TIME],FLAG from tb) as a ) as b
) A PIVOT (MAX([time]) FOR sj IN (' + @SQL + ')) B
')
-----------------------------
ID DUTY_DATE FLAG Time1 Time2 Time3 Time4
----------- ---------- ----- -------- -------- -------- --------
18801155 2011-11-2 出 10:03:16 16:11:02 16:30:02 NULL
18801155 2011-11-2 入 07:58:32 12:43:30 16:13:18 17:13:42
18801156 2011-11-2 出 10:03:16 NULL NULL NULL
18801156 2011-11-2 入 07:58:32 12:43:30 NULL NULL
18801157 2011-11-2 出 16:11:02 NULL NULL NULL
18801157 2011-11-2 入 16:13:18 NULL NULL NULL(6 行受影响)