2008 R2 Standard 多行转一列

[mycodeis0000]

2008 R2 Standard 多行转一列


我使用SQL2005 执行下面这行SQL
select top 1 FID,
(select STM.FName+' ;' from (select e.FName ,t.FID from CRM_SalesTeam t inner join CRM_SalesTeamMembers st on(st.FID=t.FID)
inner join t_Emp e on st.FMembers=e.FItemID) STM FOR XML PATH('')) AS UserList from CRM_SalesTeam


如果有2行数据
张三
李四

输出结果
张三;李四;


但使用2008 R2 Standard
得出的结果却是
张三;李四;张三;李四;张三;李四;这样重复的循环

[FlySQL]

try

select top 1 FID,
(select STM.FName+' ;' from 
(select distinct e.FName ,t.FID from CRM_SalesTeam t inner join CRM_SalesTeamMembers st on(st.FID=t.FID)
inner join t_Emp e on st.FMembers=e.FItemID
) STM FOR XML PATH('')) AS UserList from CRM_SalesTeam

[-晴天]

--任意行转置成任意列
create table tb(col int)
insert into tb select 2
insert into tb select 2
insert into tb select 4
insert into tb select 7
insert into tb select 34
insert into tb select 23
insert into tb select 44
insert into tb select 12
insert into tb select 3
insert into tb select 6
insert into tb select 8
insert into tb select 19
go
select id=identity(int,1,1),col into ##1 from tb
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+'['+ ltrim(id) +']' from(
select distinct [id] from ##1
)t
exec('select '+@s+' into ##2 from ##1 pivot (max([col]) for id in('+@s+'))b')
select * from ##2
/*
1           2           3           4           5           6           7           8           9           10          11          12
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2           2           4           7           34          23          44          12          3           6           8           19

(1 行受影响)
*/
go
drop table tb,##1,##2

[Pro_w]

[Quote=引用 2 楼 qianjin036a 的回复:]
SQL code

--任意行转置成任意列
create table tb(col int)
insert into tb select 2
insert into tb select 2
insert into tb select 4
insert into tb select 7
insert into tb select 34
insert into tb select 2……
[/Quote]

[-晴天]

--任意单行转置成任意列
create table tb(col varchar(10))
insert into tb select '2'
insert into tb select '2'
insert into tb select '4'
insert into tb select '7'
insert into tb select '34'
insert into tb select '23'
insert into tb select '44'
insert into tb select '12'
insert into tb select '3'
insert into tb select '6'
insert into tb select '8'
insert into tb select '19'
go
select id=identity(int,1,1),col into ##1 from tb
declare @s nvarchar(4000),@s1 nvarchar(4000)
select @s=isnull(@s+'+'',''+','')+'['+ ltrim(id) +']' from(
select distinct [id] from ##1
)t
select @s1=isnull(@s1+',','')+'['+ ltrim(id) +']' from(
select distinct [id] from ##1
)t
exec ('select '+@s+' col into ##2 from ##1 pivot (max([col]) for id in('+@s1+'))b')
select * from ##2
/*
1           2           3           4           5           6           7           8           9           10          11          12
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2           2           4           7           34          23          44          12          3           6           8           19

(1 行受影响)
*/
go
drop table tb,##1,##2

[-晴天]

--任意单行转置成任意列
create table tb(col varchar(10))
insert into tb select '2'
insert into tb select '2'
insert into tb select '4'
insert into tb select '7'
insert into tb select '34'
insert into tb select '23'
insert into tb select '44'
insert into tb select '12'
insert into tb select '3'
insert into tb select '6'
insert into tb select '8'
insert into tb select '19'
go
select id=identity(int,1,1),col into ##1 from tb
declare @s nvarchar(4000),@s1 nvarchar(4000)
select @s=isnull(@s+'+'',''+','')+'['+ ltrim(id) +']' from(
select distinct [id] from ##1
)t
select @s1=isnull(@s1+',','')+'['+ ltrim(id) +']' from(
select distinct [id] from ##1
)t
exec ('select '+@s+' col into ##2 from ##1 pivot (max([col]) for id in('+@s1+'))b')
select * from ##2
/*
col
-----------------------------------------------------------------------------------------------------------------------------------
2,2,4,7,34,23,44,12,3,6,8,19

(1 行受影响)

*/
go
drop table tb,##1,##2

[mycodeis0000]

各位我还在拷贝虚拟机
你们使用的R2 2008 数据库么？

[--小F--]

[Quote=引用 6 楼 mycodeis0000 的回复:]
各位我还在拷贝虚拟机
你们使用的R2 2008 数据库么？
[/Quote]
是的

[mycodeis0000]

问题解决是~
忘记分组了