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
得出的结果却是
张三;李四;张三;李四;张三;李四;这样重复的循环
我使用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
得出的结果却是
张三;李四;张三;李四;张三;李四;这样重复的循环
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
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