现在有这些数据bookid type grade
1 01 0
1 02 2
1 03 1
2 02 1
2 01 0
..............
..........
想输出结果:
bookid type
1 01:03:02
2 01:02
type是按grade升序连接的。-----------请高手帮忙!!
现在我跟据网友给我提供的方法已然在我这里运行不成功,请高手帮我解决一下。
网友提供的解决方法:
if not object_id('tb') is null
drop table tb
Go
Create table tb([bookid] int,[type] nvarchar(2),[grade] int)
Insert tb
select 1,N'01',0 union all
select 1,N'02',2 union all
select 1,N'03',1 union all
select 2,N'02',1 union all
select 2,N'01',0
Go
Select [bookid],
[type]=stuff((select ':'+[type]
from tb
where [bookid]=t.[bookid] order by [grade]
for xml path('')),1,1,'')
from tb t
group by [bookid]我用的是sql 2008 报:在关键字 'for' 附近有语法错误。
1 01 0
1 02 2
1 03 1
2 02 1
2 01 0
..............
..........
想输出结果:
bookid type
1 01:03:02
2 01:02
type是按grade升序连接的。-----------请高手帮忙!!
现在我跟据网友给我提供的方法已然在我这里运行不成功,请高手帮我解决一下。
网友提供的解决方法:
if not object_id('tb') is null
drop table tb
Go
Create table tb([bookid] int,[type] nvarchar(2),[grade] int)
Insert tb
select 1,N'01',0 union all
select 1,N'02',2 union all
select 1,N'03',1 union all
select 2,N'02',1 union all
select 2,N'01',0
Go
Select [bookid],
[type]=stuff((select ':'+[type]
from tb
where [bookid]=t.[bookid] order by [grade]
for xml path('')),1,1,'')
from tb t
group by [bookid]我用的是sql 2008 报:在关键字 'for' 附近有语法错误。
drop table tb
Go
Create table tb([bookid] int,[type] nvarchar(2),[grade] int)
Insert tb
select 1,N'01',0 union all
select 1,N'02',2 union all
select 1,N'03',1 union all
select 2,N'02',1 union all
select 2,N'01',0
Go
Select [bookid],
[type]=stuff((select ':'+[type]
from tb
where [bookid]=t.[bookid] order by [grade]
for xml path('')),1,1,'')
from tb t
group by [bookid]我这测试正常
我也是SQL 2008
---贴出下面语句你的运行结果
SELECT @@VERSION
stuff for xml path??