--> 数据库版本: --> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 --> 测试数据:[TB] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') AND type in (N'U')) DROP TABLE [TB] GO---->建表 create table [TB]([name] varchar(4),[status] varchar(8)) insert [TB] select '小张','普通员工' union all select '小张','组长' union all select '小二','经理' union all select '小三','老板' union all select '小三','员工' GO SELECT a.name, status =stuff(( select ','+convert(varchar(20),status) FROM [TB] WHERE name = a.name for xml path('')) ,1,1,'') FROM [TB] a group by a.name --> 查询结果 SELECT * FROM [TB] --> 删除表格 --DROP TABLE [TB]
--> 数据库版本: --> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 --> 测试数据:[TB] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') AND type in (N'U')) DROP TABLE [TB] GO---->建表 create table [TB]([name] varchar(4),[status] varchar(8)) insert [TB] select '小张','普通员工' union all select '小张','组长' union all select '小二','经理' union all select '小三','老板' union all select '小三','员工' GO SELECT a.name, status =stuff(( select ','+convert(varchar(20),status) FROM [TB] WHERE name = a.name for xml path('')) ,1,1,'') FROM [TB] a group by a.nameSELECT a.name, status =STUFF(REPLACE(REPLACE( (select status FROM [TB] where name = a.name FOR XML AUTO ), '<TB status="', ','), '"/>', ''), 1, 1, '') FROM [TB] a group by a.name --> 查询结果 SELECT * FROM [TB] --> 删除表格 --DROP TABLE [TB]
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([name] varchar(4),[status] varchar(8)) insert [TB] select '小张','普通员工' union all select '小张','组长' union all select '小二','经理' union all select '小三','老板' union all select '小三','员工'select * from [TB] select [name], [values]=stuff((select ','+[status] from tb t where [name]=tb.[name] for xml path('')), 1, 1, '') from tb group by [name] /* name values ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 小二 经理 小三 老板,员工 小张 普通员工,组长(3 行受影响) */ drop table tb
select b.name, case when b.name='СÕÅ' then concat(b.status1,b.status2) end status , case when b.name='СÈý' then concat(b.status4,b.status5) end status, case when b.name='С¶þ' then b.status3 end status from (select t.name, max(case when t.name='СÕÅ' and t.rn=1 then status else 'null' end) status1, max(case when t.name='СÕÅ' and t.rn=2 then status else 'null' end) status2, max(case when t.name='С¶þ' and t.rn=1 then status else 'null' end) status3, max(case when t.name='СÈý' and t.rn=1 then status else 'null' end) status4, max(case when t.name='СÈý' and t.rn=2 then status else 'null' end) status5 from (select row_number() over(partition by name order by status) rn,t4.* from t4) t group by name) b
if(object_id('person') is not null) drop table person create table person(name varchar(10),status varchar(10)) insert into person select '小张','普通员工' union all select '小张', '组长' union all select '小二','经理' union all select '小三','老板' union all select '小三','员工'if(object_id('GetPositionByName') is not null) drop function GetPositionByName go create function GetPositionByName(@name varchar(10)) returns varchar(10) as begin declare @position varchar(10) set @position = '' select @position = @position + ',' + isnull(status,'') from person where name = @name select @position = stuff(@position,1,1,'') return(@position) endselect name,dbo.GetPositionByName(name) status from person group by name
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A GOCREATE TABLE A ( NAME VARCHAR(20), STATU VARCHAR(50) ) GOINSERT INTO A SELECT '小张','普通员工' UNION ALL SELECT '小张','组长' UNION ALL SELECT '小二','经理' UNION ALL SELECT '小三','老板' UNION ALL SELECT '小三','员工' GOSELECT DISTINCT NAME,RIGHT(STATUS,LEN(STATUS)-1) FROM ( SELECT *, STATUS=(SELECT ','+LTRIM(STATU) FROM A WHERE NAME=T.NAME FOR XML PATH('')) FROM A T ) A GO
看来要研究一下stuff,最近这个函数老出现!
前几楼都回答正确,用stuff函数
楼上的几位问一下,其中的 xml是sqlserver2008新赠的吗? 我用05 提示错误 Msg 170, Level 15, State 1, Line 3 第 3 行: 'xml' 附近有语法错误。 菜鸟飞过!!!!!!!!!!!!!!!!!!!!!!!!!!!!11
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([name] varchar(4),[status] varchar(8))
insert [TB]
select '小张','普通员工' union all
select '小张','组长' union all
select '小二','经理' union all
select '小三','老板' union all
select '小三','员工'
GO
SELECT a.name,
status =stuff((
select ','+convert(varchar(20),status) FROM [TB] WHERE name = a.name for xml path(''))
,1,1,'')
FROM [TB] a
group by a.name
--> 查询结果
SELECT * FROM [TB]
--> 删除表格
--DROP TABLE [TB]
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([name] varchar(4),[status] varchar(8))
insert [TB]
select '小张','普通员工' union all
select '小张','组长' union all
select '小二','经理' union all
select '小三','老板' union all
select '小三','员工'
GO
SELECT a.name,
status =stuff((
select ','+convert(varchar(20),status) FROM [TB] WHERE name = a.name for xml path(''))
,1,1,'')
FROM [TB] a
group by a.nameSELECT a.name,
status =STUFF(REPLACE(REPLACE(
(select status
FROM [TB]
where name = a.name
FOR XML AUTO
), '<TB status="', ','), '"/>', ''), 1, 1, '')
FROM [TB] a
group by a.name
--> 查询结果
SELECT * FROM [TB]
--> 删除表格
--DROP TABLE [TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([name] varchar(4),[status] varchar(8))
insert [TB]
select '小张','普通员工' union all
select '小张','组长' union all
select '小二','经理' union all
select '小三','老板' union all
select '小三','员工'select * from [TB]
select [name], [values]=stuff((select ','+[status] from tb t where [name]=tb.[name] for xml path('')), 1, 1, '')
from tb
group by [name] /*
name values
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
小二 经理
小三 老板,员工
小张 普通员工,组长(3 行受影响)
*/
drop table tb
case when b.name='СÕÅ' then concat(b.status1,b.status2) end status ,
case when b.name='СÈý' then concat(b.status4,b.status5) end status,
case when b.name='С¶þ' then b.status3 end status
from
(select t.name,
max(case when t.name='СÕÅ' and t.rn=1 then status else 'null' end) status1,
max(case when t.name='СÕÅ' and t.rn=2 then status else 'null' end) status2,
max(case when t.name='С¶þ' and t.rn=1 then status else 'null' end) status3,
max(case when t.name='СÈý' and t.rn=1 then status else 'null' end) status4,
max(case when t.name='СÈý' and t.rn=2 then status else 'null' end) status5
from
(select row_number() over(partition by name order by status) rn,t4.*
from t4) t group by name) b
if(object_id('person') is not null) drop table person
create table person(name varchar(10),status varchar(10))
insert into person
select '小张','普通员工' union all
select '小张', '组长' union all
select '小二','经理' union all
select '小三','老板' union all
select '小三','员工'if(object_id('GetPositionByName') is not null) drop function GetPositionByName
go
create function GetPositionByName(@name varchar(10))
returns varchar(10)
as
begin
declare @position varchar(10)
set @position = ''
select @position = @position + ',' + isnull(status,'') from person where name = @name
select @position = stuff(@position,1,1,'')
return(@position)
endselect name,dbo.GetPositionByName(name) status from person group by name
IF OBJECT_ID('A') IS NOT NULL
DROP TABLE A
GOCREATE TABLE A
(
NAME VARCHAR(20),
STATU VARCHAR(50)
)
GOINSERT INTO A
SELECT '小张','普通员工'
UNION ALL
SELECT '小张','组长'
UNION ALL
SELECT '小二','经理'
UNION ALL
SELECT '小三','老板'
UNION ALL
SELECT '小三','员工'
GOSELECT DISTINCT NAME,RIGHT(STATUS,LEN(STATUS)-1)
FROM
(
SELECT *,
STATUS=(SELECT ','+LTRIM(STATU) FROM A WHERE NAME=T.NAME FOR XML PATH(''))
FROM A T
) A
GO
我用05 提示错误
Msg 170, Level 15, State 1, Line 3
第 3 行: 'xml' 附近有语法错误。 菜鸟飞过!!!!!!!!!!!!!!!!!!!!!!!!!!!!11