求一条复杂的SQL查询语句,交叉表的动态查询

[iori_guojun]

原始表的数据如下：
PID PDatE PTime
111111 2003-01-28 04:30:09
111111 2003-01-28 18:30:00
222222 2003-01-28 04:31:09
333333 2003-01-28 04:32:09
111111 2003-02-09 03:35:25
222222 2003-02-09 03:36:25
333333 2003-02-09 03:37:25

查询生成表
PDate 111111 222222 333333 ......
2003-01-28 04:30:09 04:31:09 04:32:09 ......
2003-01-28 18:30:00 null null
2003-02-09 03:35:25 03:36:25 03:37:25 ......

[iori_guojun]

請大家幫忙看看

[tiandiqing]

select PDate,case when PID='111111' then PTime else end, case when PID='22222' then PTime else end,case when PID='33333' then PTime else end from table group by pdate

我瞎写的，不知道对不对

[tiandiqing]

select PDate,(case when PID='111111' then PTime else null end) as 11111, (case when PID='22222' then PTime else null end) as 22222,(case when PID='33333' then PTime else null end) as 33333 from table group by pdate

[iori_guojun]

應該不是這樣的，它是動態的。

[paoluo]

Create Table TEST
(PIDPDatE Nvarchar(10),
PTime DateTime)
Insert TEST Select '111111','2003-01-28 04:30:09'
Union All Select '111111','2003-01-28 18:30:00'
Union All Select '222222','2003-01-28 04:31:09'
Union All Select '333333','2003-01-28 04:32:09'
Union All Select '111111','2003-02-09 03:35:25'
Union All Select '222222','2003-02-09 03:36:25'
Union All Select '333333','2003-02-09 03:37:25'
GO
Declare @S Varchar(8000)
Select @S=''
Select *,ID=(Select Count(*) From TEST Where PIDPDatE=A.PIDPDatE And DateDiff(dd,PTime,A.PTime)=0 And PTime<=A.PTime) Into #T From TEST A
Select @S=@S+',Max(Case When PIDPDatE='''+PIDPDatE+''' Then Convert(Varchar,PTime,108) Else Null End) As ['+PIDPDatE+']'
From TEST Group By PIDPDatE Order By PIDPDatE
Select @S='Select Convert(Varchar(10),PTime,120) As PDate'+@S+' From #T Group By Convert(Varchar(10),PTime,120),ID Order By PDate '
EXEC(@S)
Drop Table #T
GO
Drop Table TEST
GO
/*
PDate 111111 222222 333333
2003-01-28 04:30:09 04:31:09 04:32:09
2003-01-28 18:30:00 NULL NULL
2003-02-09 03:35:25 03:36:25 03:37:25
*/

[iori_guojun]

PID PTime PNo
111111 2003-01-28 04:30:09
111111 2003-01-28 18:30:00
222222 2003-01-28 04:31:09
333333 2003-01-28 04:32:09
111111 2003-02-09 03:35:25
222222 2003-02-09 03:36:25
333333 2003-02-09 03:37:25
數據是這樣的

[paoluo]

Create Table TEST
(PID Nvarchar(10),
PDate Nvarchar(10),
PTime Nvarchar(8))
Insert TEST Select '111111','2003-01-28','04:30:09'
Union All Select '111111','2003-01-28', '18:30:00'
Union All Select '222222','2003-01-28', '04:31:09'
Union All Select '333333','2003-01-28', '04:32:09'
Union All Select '111111','2003-02-09', '03:35:25'
Union All Select '222222','2003-02-09', '03:36:25'
Union All Select '333333','2003-02-09', '03:37:25'
GO
Declare @S Varchar(8000)
Select @S=''
Select *,ID=(Select Count(*) From TEST Where PID=A.PID And PDate=A.PDate And PTime<=A.PTime) Into #T From TEST A
Select @S=@S+',Max(Case When PID='''+PID+''' Then PTime Else Null End) As ['+PID+']'
From TEST Group By PID Order By PID
Select @S='Select PDate'+@S+' From #T Group By PDate,ID Order By PDate '
Print @S
EXEC(@S)
Drop Table #T
GO
Drop Table TEST
GO
/*
PDate 111111 222222 333333
2003-01-28 04:30:09 04:31:09 04:32:09
2003-01-28 18:30:00 NULL NULL
2003-02-09 03:35:25 03:36:25 03:37:25
*/

[iori_guojun]

對的，請問Max有什麼用？

[iori_guojun]

能不能不用臨時表啊

[liu20011122]

请问如果用 MySlq 数据库怎么实现?

[iori_guojun]

一樣，改成MySlq語法就行。

[paoluo]

不用臨時表可以，但是不用臨時表語句就很長，麻煩很多。

為什麼不能用臨時表？？

[iori_guojun]

沒什麼。。。。。謝謝。。接分。。