22,302
社区成员




--构建测试数据
create table #TB(房号 int, 床号 int, 姓名 varchar(10))
insert into #TB select 201, 0, '张三'
union all select 201, 1, '张四'
union all select 201, 2, '张五'
union all select 202, 0, '张六'
union all select 202, 1, '张七'
union all select 202, 2, '张八'
--效果一解决方案
select 房号,0 as 床号,[0] as 姓名, 1 as 床号, [1] as 姓名 , 2 as 床号,[2] as 姓名
from #TB pivot (max(姓名)for 床号 in ([0],[1],[2]))p
/*
房号 床号 姓名 床号 姓名 床号 姓名
----------- ----------- ---------- ----------- ---------- ----------- ----------
201 0 张三 1 张四 2 张五
202 0 张六 1 张七 2 张八
(2 行受影响)
*/
--效果二解决方案
select 房号,[0] as 床号0,[1] as 床号1,[2] as 床号2 from #TB pivot (max(姓名)for 床号 in ([0],[1],[2]) )p
/*
房号 床号0 床号1 床号2
----------- ---------- ---------- ----------
201 张三 张四 张五
202 张六 张七 张八
(2 行受影响)
*/
--SQL Server 2000
create function dbo.f_str()
returns varchar(100)
as
begin
declare @str varchar(100)
select @str=isnull(@str+'\','')+rtrim(字段) from tb
return @str --少了return,呵呵..
end
go
select 字段=dbo.f_str() from tb
--SQL Server 2000
create function dbo.f_str()
returns varchar(100)
as
begin
declare @str varchar(100)
select @str=isnull(@str+'\','')+rtrim(字段) from tb
end
go
select 字段=dbo.f_str() from tb
--SQL Server 2005
select 字段=stuff((select '\'+rtrim(字段) from tb for xml path('')),1,1,'') from 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.name
SELECT 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]