多行数据转化为一行，有没有比较好的方法？
两个表A、B
表A:
ID Name
1 Rober
2 Luck
表B:
ID Role
1 信息录入员
1 信息审核员
2 广告维护员
2 论坛管理员
2 博客管理员
.
.
关系：表A与表B通过ID一对多关系
需要：
查询出来的效果如下：
1 Rober 信息录入员、信息审核员
2 Luck 广告维护员、论坛管理员、博客管理员....
请高人指点，欢迎顶帖接分！
[解决办法]
如果是SQL 2000，就必須借助函數實現。
[解决办法]
Create Function F_GetRole(@ID Int)
Returns Nvarchar(2000)
As
Begin
Declare @S Nvarchar(2000)
Select @S = ' '
Select @S = @S + ', ' + Role From B Where ID = @ID
Select @S = Stuff(@S, 1, 1, ' ')
Return @S
End
GO
Select
ID,
Name,
dbo.F_GetRole(ID) As Role
From
A
[解决办法]
Create Table A
(IDInt,
NameVarchar(20))
Insert A Select 1, 'Rober '
Union All Select 2, 'Luck '
Create Table B
(IDInt,
RoleNvarchar(20))
Insert B Select 1, N '信息录入员 '
Union All Select 1, N '信息审核员 '
Union All Select 2, N '广告维护员 '
Union All Select 2, N '论坛管理员 '
Union All Select 2, N '博客管理员 '
GO
Create Function F_GetRole(@ID Int)
Returns Nvarchar(2000)
As
Begin
Declare @S Nvarchar(2000)
Select @S = ' '
Select @S = @S + ', ' + Role From B Where ID = @ID
Select @S = Stuff(@S, 1, 1, ' ')
Return @S
End
GO
Select
ID,
Name,
dbo.F_GetRole(ID) As Role
From
A
GO
Drop Table A, B
Drop Function F_GetRole
--Result
/*
IDNameRole
1Rober信息录入员,信息审核员
2Luck广告维护员,论坛管理员,博客管理员
*/
[解决办法]
--带符号合并行列转换
--有表t，其数据如下：
a b
1 1
1 2
1 3
2 1
2 2
3 1
--如何转换成如下结果:
a b
1 1,2,3
2 1,2
3 1
create table tb
(
a int,
b int
)
insert into tb(a,b) values(1,1)
insert into tb(a,b) values(1,2)
insert into tb(a,b) values(1,3)
insert into tb(a,b) values(2,1)
insert into tb(a,b) values(2,2)
insert into tb(a,b) values(3,1)
go
if object_id( 'pubs..f_hb ') is not null
drop function f_hb
go
--创建一个合并的函数
create function f_hb(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + cast(b as varchar) from tb where a = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
--调用自定义函数得到结果：
select distinct a ,dbo.f_hb(a) as b from tb
drop table tb
--结果
a b
----------- ------
1 1,2,3
2 1,2
3 1
（所影响的行数为 3 行）
多个前列的合并
数据的原始状态如下：
ID PR CON OP SC
001 p c 差 6
001 p c 好 2
001 p c 一般 4
002 w e 差 8
002 w e 好 7
002 w e 一般 1
===========================
用SQL语句实现，变成如下的数据
ID PR CON OPS
001 p c 差(6),好(2),一般(4)
002 w e 差(8),好(7),一般(1)
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb
(
id varchar(10),
pr varchar(10),
con varchar(10),
op varchar(10),
sc int
)
insert into tb(ID,PR,CON,OP,SC) values( '001 ', 'p ', 'c ', '差 ', 6)
insert into tb(ID,PR,CON,OP,SC) values( '001 ', 'p ', 'c ', '好 ', 2)
insert into tb(ID,PR,CON,OP,SC) values( '001 ', 'p ', 'c ', '一般 ', 4)
insert into tb(ID,PR,CON,OP,SC) values( '002 ', 'w ', 'e ', '差 ', 8)
insert into tb(ID,PR,CON,OP,SC) values( '002 ', 'w ', 'e ', '好 ', 7)
insert into tb(ID,PR,CON,OP,SC) values( '002 ', 'w ', 'e ', '一般 ', 1)
go
if object_id( 'pubs..test ') is not null
drop table test
go
select ID,PR,CON , OPS = op + '( ' + cast(sc as varchar(10)) + ') ' into test from tb
--创建一个合并的函数
if object_id( 'pubs..f_hb ') is not null
drop function f_hb
go
create function f_hb(@id varchar(10),@pr varchar(10),@con varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + cast(OPS as varchar) from test where id = @id and @pr = pr and @con = con
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
--调用自定义函数得到结果：
select distinct id ,pr , con , dbo.f_hb(id,pr,con) as OPS from test
drop table tb
drop table test
--结果
id pr con OPS
---------- ---------- ---------- -------------------
001 p c 差(6),好(2),一般(4)
002 w e 差(8),好(7),一般(1)
（所影响的行数为 2 行）
create table b
(col varchar(20))
insert b values ( 'a ')
insert b values ( 'b ')
insert b values ( 'c ')
insert b values ( 'd ')
insert b values ( 'e ')
declare @sql varchar(1024)
set @sql= ' '
select @sql=@sql+b.col+ ', ' from (select col from b) as b
set @sql= 'select ' ' '+@sql+ ' ' ' '
exec(@sql)
[解决办法]
if object_id( 'pubs..A ') is not null
drop table A
go
create table A(ID int,Name varchar(10))
insert into A values(1, 'Rober ')
insert into A values(2, 'Luck ')
go
if object_id( 'pubs..B ') is not null
drop table B
go
create table B(ID int,Role varchar(10))
insert into B values(1, '信息录入员 ')
insert into B values(1, '信息审核员 ')
insert into B values(2, '广告维护员 ')
insert into B values(2, '论坛管理员 ')
insert into B values(2, '博客管理员 ')
go
if object_id( 'pubs..f_hb ') is not null
drop function f_hb
go
--创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ' ' + cast(role as varchar) from B where id = @id
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
--调用自定义函数得到结果：
select a.id , a.name , t.role from a,
(select distinct id ,dbo.f_hb(id) as role from B) t
where a.id = t.id
drop table A,B
/*
id name role
----------- ---------- --------------------------------
1 Rober 信息录入员 信息审核员
2 Luck 广告维护员 论坛管理员 博客管理员
（所影响的行数为 2 行）
*/
[解决办法]
还顶什么，这鱼弄得我写好了都帖不成，我去吃饭了
[解决办法]
O_o
[解决办法]
呵呵 bs一下红猩猩们抢分 :)
[解决办法]
同意，我接分
[解决办法]
又回来了，其实对大家好事，可以多学习
[解决办法]
Create Table B
(ID Int,
Role Nvarchar(20))
Insert B Select 1, N '信息录入员 '
Union All Select 1, N '信息审核员 '
Union All Select 2, N '广告维护员 '
Union All Select 2, N '论坛管理员 '
Union All Select 2, N '博客管理员 '
go
declare @col1 int
declare @ret Nvarchar(4000)
set @col1=0
set @ret= ' '
update b
set role= @ret,
@ret=case when @col1=ID then @Ret+ ', '+role else role end,
@col1=ID
select * from b
drop table b
[解决办法]
晚了一点
[解决办法]
关注
[解决办法]
借LZ地盘问个问题 谢谢解答
创建函数F_GetRole()中
--------------------------------------------------
Select @S = ' '
Select @S = @S + ', ' + Role From B Where ID = @ID
Select @S = Stuff(@S, 1, 1, ' ')
Return @S
--------------------------------------------------
Select @S = @S + ', ' + Role From B Where ID = @ID 是怎样实现把多个Role中的值放到@中的？（这个语句里并没有一个递归调用）
（想了半天都没思绪）谢谢解答
[解决办法]
以下只是一个思路而矣
create table #
(
a int,
b varchar(10)
)
insert into #
select 1, 'a1 ' union all
select 2, 'a2 ' union all
select 3, 'a3 ' union all
select 4, 'a4 ' union all
select 5, 'a5 '
select *
from #
declare @str varchar(10)
set @str= ' '
select @str=@str+ ', '+b
--into #2
from #
print @str
drop table #
[解决办法]
借LZ地盘问个问题 谢谢解答
创建函数F_GetRole()中
--------------------------------------------------
Select @S = ' '
Select @S = @S + ', ' + Role From B Where ID = @ID
Select @S = Stuff(@S, 1, 1, ' ')
Return @S
--------------------------------------------------
Select @S = @S + ', ' + Role From B Where ID = @ID 是怎样实现把多个Role中的值放到@中的？（这个语句里并没有一个递归调用）
====================
呵呵,确实是类似于循环的.是有这种用法的.但是对有些情况下是不可用的.
你看看邹健的blog上,专门一文章说这个问题
[解决办法]
Select @S = @S + ', ' + Role From B Where ID = @ID
很简单的，是把ID = @ID便利的Role自动累计到@S里
就简单看Select Role From B Where ID = @ID 是把ID = @ID的role都查列出来
[解决办法]
create table tableint(ID int , Name varchar(20))
INSERT INTO TABLEINT
SELECT 1 , ' Rober ' UNION SELECT
2 , 'Luck '
CREATE TABLE TABLEROLE(ID INT,ROLE VARCHAR(20))
INSERT INTO TABLEROLE
SELECT 1, 'AAAA ' UNION ALL SELECT
1 , 'BBBB ' UNION ALL SELECT
2 , 'CCCC ' UNION ALL SELECT
2 , 'DDDDD ' UNION ALL SELECT
2 , 'EEEEEE '
CREATE function f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + ROLE from TABLEROLE where id = @id
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
SELECT A.ID,A.NAME,B.ROLE FROM TABLEINT A,
(SELECT DISTINCT ID ,dbo.f_hb(ID) AS ROLE FROM TABLEROLE) B
WHERE A.ID =B.ID
