sql server 语句报错， 各位大神来看看，，
[解决办法]
declare @table table
(
id int,
sid nvarchar(5),
result nvarchar(10)
)
insert into @table(id,sid,result)
select 1 ,'001' ,'80.0' union all
select 2 ,'001' ,'90.0' union all
select 3 ,'001' ,'80.0' union all
select 4 ,'002' ,'56.0' union all
select 5 ,'002' ,'69.0' union all
select 6 ,'002' ,'89.0'
select sid=sid+' '+(select result+' ' from @table where sid=tab.sid for xml path(''))
from
(
select sid from @table group by sid
)tab
DECLARE @s NVARCHAR(4000)
SET @s = ''
SELECT @s = @s + ',' + QUOTENAME([sid]) + '=max(case when [sid]='
+ QUOTENAME([sid], '''') + ' then [result] else 0.0 end)'
FROM tb
GROUP BY [sid]
SELECT @s = SUBSTRING(@s, 2, LEN(@s))
EXEC('select '+@s+' from tb ')
declare @table table
(
id int,
sid nvarchar(5),
result nvarchar(10)
)
insert into @table(id,sid,result)
select 1 ,'001' ,'80.0' union all
select 2 ,'001' ,'90.0' union all
select 3 ,'001' ,'80.0' union all
select 4 ,'002' ,'56.0' union all
select 5 ,'002' ,'69.0' union all
select 6 ,'002' ,'89.0'
select sid=sid+' '+(select result+' ' from @table where sid=tab.sid for xml path(''))
from
(
select sid from @table group by sid
)tab
/*
sid
----------------------------------------------------------------------------------------------------------------
001 80.0 90.0 80.0
002 56.0 69.0 89.0
*/
[解决办法]
select sid,([1]+' '+[2]+' '+[3]) from (select sid,result,ROW_NUMBER() over(partition by sid order by sid )as bj from sss) as tt pivot(max(result) for bj in([1],[2],[3]))as t
00180.0 90.0 80.0
00256.0 69.0 89.0
