WITH a1 (Uname,Con) AS
(
SELECT 'admin','开机' UNION ALL
SELECT 'admin','故障' UNION ALL
SELECT 'admin','关机' UNION ALL
SELECT 'test','测试' UNION ALL
SELECT 'test','OK'
)
SELECT Uname,STUFF((SELECT ','+Con FROM a1 WHERE Uname=a.Uname FOR XML PATH('')),1,1,'')
FROM a1 a
GROUP BY Uname
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-02-19 12:26:39
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([Uname] varchar(5),[Con] varchar(4))
insert [huang]
select 'admin','开机' union all
select 'admin','故障' union all
select 'admin','关机' union all
select 'test','测试' union all
select 'test','OK'
--------------开始查询--------------------------select a.[Uname],
stuff((select ','+[Con] from [huang] b
where b.[Uname]=a.[Uname]
for xml path('')),1,1,'') [Con]
from [huang] a
group by a.[Uname]
----------------结果----------------------------
/*
Uname Con
----- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
admin 开机,故障,关机
test 测试,OK
*/
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([Uname] varchar(5),[Con] varchar(4))
insert [huang]
select 'admin','开机' union all
select 'admin','故障' union all
select 'admin','关机' union all
select 'test','测试' union all
select 'test','OK'go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 VARCHAR(50))
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+[Con] from [huang] where [Uname]=@Col1
return @S
end
go
Select distinct [Uname],[Con]=dbo.F_Str([Uname]) from [huang]/*
Uname Con
----- ----------------------------------------------------------------------------------------------------
admin 开机,故障,关机
test 测试,OK
*/
查出来的<b/>变成了< ?
----
<b/>*/你的数据有问题?
go
create table [huang]([Uname] varchar(5),[Con] varchar(4))
insert [huang]
select 'admin','开机' union all
select 'admin','故障' union all
select 'admin','关机' union all
select 'test','测试' union all
select 'test','OK'
--------------开始查询--------------------------
select a.[Uname],'<b/>'+
stuff((select ','+[Con] from [huang] b
where b.[Uname]=a.[Uname]
for xml path('')),1,1,'') [Con]
from [huang] a
group by a.[Uname]/*
Uname Con
----- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
admin <b/>开机,故障,关机
test <b/>测试,OK
*/
stuff((select [Con]+'<br/>' from [huang] b
where b.[Uname]=a.[Uname]
for xml path('')),1,1,'') [Con]
from [huang] a
group by a.[Uname]
go
create table [huang]([Uname] varchar(5),[Con] varchar(4))
insert [huang]
select 'admin','开机' union all
select 'admin','故障' union all
select 'admin','关机' union all
select 'test','测试' union all
select 'test','OK'
go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 VARCHAR(50))
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+[Con]+'<br/>' from [huang] where [Uname]=@Col1
return @S
END
go
Select distinct [Uname],[Con]=dbo.F_Str([Uname]) from [huang]/*
Uname Con
----- ----------------------------------------------------------------------------------------------------
admin 开机<br/>,故障<br/>,关机<br/>
test 测试<br/>,OK<br/>
*/
你要的是这个效果吗,试试这个:if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([Uname] varchar(5),[Con] varchar(4))
insert [huang]
select 'admin','开机' union all
select 'admin','故障' union all
select 'admin','关机' union all
select 'test','测试' union all
select 'test','OK'
--------------开始查询--------------------------
select a.[Uname],
replace((select [Con]+',' from [huang] b
where b.[Uname]=a.[Uname]
for xml path('')),',','</br>') [Con]
from [huang] a
group by a.[Uname]/*
Uname Con
admin 开机</br>故障</br>关机</br>
test 测试</br>OK</br>
*/
replace((select [Con]+',' from [huang] b
where b.[Uname]=a.[Uname]
for xml path('')),',','</br>,') [Con]
from [huang] a
group by a.[Uname]
你要的是这个效果吗,试试这个:if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([Uname] varchar(5),[Con] varchar(4))
insert [huang]
select 'admin','开机' union all
select 'admin','故障' union all
select 'admin','关机' union all
select 'test','测试' union all
select 'test','OK'
--------------开始查询--------------------------
select a.[Uname],
replace((select [Con]+',' from [huang] b
where b.[Uname]=a.[Uname]
for xml path('')),',','</br>') [Con]
from [huang] a
group by a.[Uname]/*
Uname Con
admin 开机</br>故障</br>关机</br>
test 测试</br>OK</br>
*/谢谢大家,正确