查询语句是这样:
select RoleEmp.EmployeeNo,SRole.RoleName from dbo.tSecurityRoleEmployee RoleEmp
inner join tSecurityRole SRole ON RoleEmp.RoleID = SRole.RoleID
where RoleEmp.RoleID = 'FD5A7168-ED1C-434D-95D4-099765791AD0'返回的结果是
chenjing22 工间操视频
wuyongquan 工间操视频
wuzhicheng 工间操视频我现在希望的结果是两列
第一列 第二列
chenjing22,wuyongquan,wuzhicheng 工间操视频 请问各位高手如何写?
另外,有的角色ID下人员特别多,有几千条。
select RoleEmp.EmployeeNo,SRole.RoleName from dbo.tSecurityRoleEmployee RoleEmp
inner join tSecurityRole SRole ON RoleEmp.RoleID = SRole.RoleID
where RoleEmp.RoleID = 'FD5A7168-ED1C-434D-95D4-099765791AD0'返回的结果是
chenjing22 工间操视频
wuyongquan 工间操视频
wuzhicheng 工间操视频我现在希望的结果是两列
第一列 第二列
chenjing22,wuyongquan,wuzhicheng 工间操视频 请问各位高手如何写?
另外,有的角色ID下人员特别多,有几千条。
我希望的结果是
第一列:chenjing22,wuyongquan,wuzhicheng
第二列:工间操视频
--方法0:动态SQL法
declare @s varchar(100),@sql varchar(1000)
set @s='1,2,3,4,5,6,7,8,9,10'
set @sql='select col='''+ replace(@s,',',''' union all select ''')+''''
PRINT @sql
exec (@sql)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO
--方法1:循环截取法
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO
--方法2:使用临时性分拆辅助表法
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
--创建分拆处理的辅助表(用户定义函数中只能操作表变量)
DECLARE @t TABLE(ID int IDENTITY,b bit)
INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b
INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
FROM @t
WHERE ID<=LEN(@s+'a')
AND CHARINDEX(@split,@split+@s,ID)=ID
RETURN
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1)
drop table [dbo].[tb_splitSTR]
GO
--方法3:使用永久性分拆辅助表法
--字符串分拆辅助表
SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR
FROM syscolumns a,syscolumns b
GO
--字符串分拆处理函数
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS TABLE
AS
RETURN(
SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100))
FROM tb_splitSTR
WHERE ID<=LEN(@s+'a')
AND CHARINDEX(@split,@split+@s,ID)=ID)
GO
--方法4:利用sql server2005的OUTER APPLY
CREATE FUNCTION [dbo].[ufn_SplitStringToTable]
(
@str VARCHAR(MAX) ,
@split VARCHAR(10)
)
RETURNS TABLE
AS
RETURN
( SELECT B.id
FROM ( SELECT [value] = CONVERT(XML , '<v>' + REPLACE(@str , @split , '</v><v>')
+ '</v>')
) A
OUTER APPLY ( SELECT id = N.v.value('.' , 'varchar(100)')
FROM A.[value].nodes('/v') N ( v )
) B
)
go
create table [TB] (col1 nvarchar(20),col2 nvarchar(10))
insert into [TB]
select 'chenjing22','工间操视频' union all
select 'wuyongquan','工间操视频' union all
select 'wuzhicheng','工间操视频'select * from [TB]select col1=stuff((select ','+col1 from tb t where col2=tb.col2 for xml path('')), 1, 1, '') ,col2
from tb
group by col2
/*
col1 col2
chenjing22,wuyongquan,wuzhicheng 工间操视频*/
inner join tSecurityRole SRole ON RoleEmp.RoleID = SRole.RoleID
where RoleEmp.RoleID = 'FD5A7168-ED1C-434D-95D4-099765791AD0'
我的想法是直接在原语句上操作
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test]([col1] varchar(10),[col2] varchar(10))
insert [test]
select 'chenjing22','工间操视频' union all
select 'wuyongquan','工间操视频' union all
select 'wuzhicheng','工间操视频'SELECT [col1],[col2] FROM (SELECT DISTINCT [col2] FROM [test])A
OUTER APPLY(
SELECT [col1]= STUFF(REPLACE(REPLACE(
( SELECT [col1] FROM [test] N
WHERE [col2] = A.[col2]
FOR XML AUTO
), '<N col1="', ','), '"/>', ''), 1, 1, '')
)N/*
col1 col2
----------------------------------------------------------
chenjing22,wuyongquan,wuzhicheng 工间操视频
*/
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test]([col1] varchar(10),[col2] varchar(10))
insert [test]
select 'chenjing22','工间操视频' union all
select 'wuyongquan','工间操视频' union all
select 'wuzhicheng','工间操视频'SELECT [col1],[col2] FROM (SELECT DISTINCT [col2] FROM (
select RoleEmp.EmployeeNo,SRole.RoleName
from dbo.tSecurityRoleEmployee RoleEmp
inner join tSecurityRole SRole ON RoleEmp.RoleID = SRole.RoleID
where RoleEmp.RoleID = 'FD5A7168-ED1C-434D-95D4-099765791AD0')t)A
OUTER APPLY(
SELECT [col1]= STUFF(REPLACE(REPLACE(
( SELECT [col1] FROM (
select RoleEmp.EmployeeNo,SRole.RoleName from dbo.tSecurityRoleEmployee RoleEmp
inner join tSecurityRole SRole ON RoleEmp.RoleID = SRole.RoleID
where RoleEmp.RoleID = 'FD5A7168-ED1C-434D-95D4-099765791AD0'
) N
WHERE [col2] = A.[col2]
FOR XML AUTO
), '<N col1="', ','), '"/>', ''), 1, 1, '')
)N/*
col1 col2
----------------------------------------------------------
chenjing22,wuyongquan,wuzhicheng 工间操视频
*/--把test换成你的原来的语句就好了
--2005以上版本你这样处理:
;with test
as(
select
RoleEmp.EmployeeNo,SRole.RoleName
from dbo.tSecurityRoleEmployee RoleEmp
inner join tSecurityRole SRole
ON RoleEmp.RoleID = SRole.RoleID
where RoleEmp.RoleID = 'FD5A7168-ED1C-434D-95D4-099765791AD0'
)
SELECT EmployeeNo,RoleName FROM (SELECT DISTINCT RoleName FROM [test])A
OUTER APPLY(
SELECT EmployeeNo= STUFF(REPLACE(REPLACE(
( SELECT EmployeeNo FROM [test] N
WHERE RoleName = A.RoleName
FOR XML AUTO
), '<N EmployeeNo="', ','), '"/>', ''), 1, 1, '')
)N
SELECT RoleName=stuff((select ','+RoleName from (select RoleEmp.EmployeeNo,SRole.RoleName
from dbo.tSecurityRoleEmployee RoleEmp
inner join tSecurityRole SRole ON RoleEmp.RoleID = SRole.RoleID
where RoleEmp.RoleID = 'FD5A7168-ED1C-434D-95D4-099765791AD0') t
where EmployeeNo =TMP.EmployeeNo for xml path('')), 1, 1, '')
,EmployeeNo
FROM (
select RoleEmp.EmployeeNo,SRole.RoleName from dbo.tSecurityRoleEmployee RoleEmp
inner join tSecurityRole SRole ON RoleEmp.RoleID = SRole.RoleID
where RoleEmp.RoleID = 'FD5A7168-ED1C-434D-95D4-099765791AD0')AS TMP
GROUP BY EmployeeNo
inner join tSecurityRole SRole ON RoleEmp.RoleID = SRole.RoleID
where RoleEmp.RoleID = 'FD5A7168-ED1C-434D-95D4-099765791AD0'
)
select RoleName ,RoleName=stuff((select ',' +EmployeeNo from test zz where zz.RoleName=bb.RoleName for xml path('')),1,1,'')
from test bb
group by RoleName
select RoleEmp.EmployeeNo,SRole.RoleName from dbo.tSecurityRoleEmployee RoleEmp
inner join tSecurityRole SRole ON RoleEmp.RoleID = SRole.RoleID
where RoleEmp.RoleID = 'FD5A7168-ED1C-434D-95D4-099765791AD0'
)
select distinct b.EmployeeNo,a.RoleName from c as a
cross apply(select stuff((select ','+EmployeeNo from c where c.RoleName=a.RoleName for xml path('')),1,1,'') as EmployeeNo) as b