递归查询的问题（不会，想不到）！

[rustical]

1.登记表A
bookdate(char8) timeid(char2) id(char6) status(char3)
20050102 08 000001 001
20050102 08 000002 002
20050102 08 000003 001
20050102 08 000004 001
20050102 09 000001 002
20050102 09 000002 002
20050102 09 000003 003
20050102 09 000004 001
......
20050102 16 000001 001
20050102 16 000002 002
20050102 16 000003 001
20050102 16 000004 001

2.id 代码表
id idname
000001 'test1'
000002 'test2'
000003 'test3'
.....

3. timeid 代码表
timeid char(2) timename char(30)
08 'jjjjj'
09 'ttttt'
10 'mmmmm'
....
16 'lllll'

如何得到监控结果：
idname 08 09 10 ... 15 16
test1 001 001 001 002 001
test2 002 002 001 002 001
test3 001 003 001 002 001
....
test13 001 002 001 001 001

[rustical]

注意：登记表A 中的timeid(char2)如果没有登记，则结果显示为空！

[子陌红尘]

--生成测试数据
create table A(
bookdate varchar(20),
timeid varchar(20),
id varchar(20),
status varchar(20))

insert into A select '20050102', '08','000001','001'
insert into A select '20050102', '08','000002','002'
insert into A select '20050102', '08','000003','001'
insert into A select '20050102', '08','000004','001'
insert into A select '20050102', '09','000001','002'
insert into A select '20050102', '09','000002','002'
insert into A select '20050102', '09','000003','003'
insert into A select '20050102', '09','000004','001'
insert into A select '20050102', '16','000001','001'
insert into A select '20050102', '16','000002','002'
insert into A select '20050102', '16','000003','001'
insert into A select '20050102', '16','000004','001'

create table ID(
id varchar(20),
idname varchar(20))

insert into ID select '000001', 'test1'
insert into ID select '000002', 'test2'
insert into ID select '000003', 'test3'

create table TIMEID(
timeid varchar(20),
timename varchar(20))

insert into TIMEID select '08','jjjjj'
insert into TIMEID select '09','ttttt'
insert into TIMEID select '10','mmmmm'
insert into TIMEID select '16','lllll'


--执行查询：
declare @s varchar(8000)
set @s = ''
select @s = @s + ',['+timeid+'] = max(case when A.timeid = '''+timeid+''' then A.status end) ' from timeid 代码表 order by timeid
exec('select b.idname'+@s+' from A , ID b where a.id = b.id group by b.idname')

[july]

临时表实现，楼上的先

[rustical]

没有说清楚，登记表A中有很多日期，我每次取某一天的日期！

[rustical]

以 id 排序，不要用 idname 排序

[子陌红尘]

--执行查询：
declare @s varchar(8000)
set @s = ''
select @s = @s + ',['+timeid+'] = max(
case when
A.timeid = '''+timeid+''' then A.status
end)'
from timeid order by timeid

set @s = ' select b.idname' + @s + ' from A ,ID b where A.id = b.id '

--增加"取某一天的日期"的条件限制
set @s = @s + ' and A.bookdate = ''20050102'''

set @s = @s + ' group by b.idnam'

exec(@s)

[rustical]

太谢谢了！

[子陌红尘]

declare @s varchar(8000)
set @s = ''
select @s = @s + ',['+timeid+'] = max(
case when
A.timeid = '''+timeid+''' then A.status
end)'
from timeid order by timeid

set @s = ' select b.idname' + @s + ' from A ,ID b where A.id = b.id '

--增加"取某一天的日期"的条件限制
set @s = @s + ' and A.bookdate = ''20050102'''

--以 id 排序，不要用 idname 排序
set @s = @s + ' group by b.id ,b.idname order by b.id'

exec(@s)