有表如下
表1:
ID (主键) NAME
1 张
2 张
3 王 表2:
Type ID
A 1
B 1
B 2
C 3
B 3
两表通过ID关联查询此表 要求输出结果表是:ID NAME TYPE
1 张 A;B
2 张 B
3 王 B;C要怎么写SQL阿?谢谢各位大侠!!
表1:
ID (主键) NAME
1 张
2 张
3 王 表2:
Type ID
A 1
B 1
B 2
C 3
B 3
两表通过ID关联查询此表 要求输出结果表是:ID NAME TYPE
1 张 A;B
2 张 B
3 王 B;C要怎么写SQL阿?谢谢各位大侠!!
insert into tt select 1,'张' union all
select 2,'张' union all
select 3,'王' create table tb (Type varchar(10),ID int)
insert into tb select 'A',1 union all
select 'B',1 union all
select 'B',2 union all
select 'C',3 union all
select 'B',3 create function dbo.fc_str(@id varchar(100))
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+';'+cast(type as varchar(100)) from tb where id=@id
return stuff(@sql,1,1,'')
endselect a.id,b.name,dbo.fc_str(a.id) as type from tb a,tt b where a.id=b.id group by a.id,b.name order by a.id
returns varchar(100)
as
begin
declare @sql varchar(100)
set @sql=''
select @sql=@sql+';'+type from 表2 where id=@id
return(left(stuff(@sql,1,1,''),len(@sql)-2))
end
goselect b.id,a.[name],dbo.f_union(b.id) as type from 表1 a,表2 b where a.id=b.id group by b.id
2006年1月的“较复杂的ORACLE行列转换”
---------------调用函数和游标实现
ID (主键) NAME
1 张
2 张
3 王 表2:
Type IDcreate function f_getString(@id int)
returns string
as
begin
declare @s varchar(100)
set @s=''
select @s=@s+';'+type from 表2 order by type
return(stuff(@s,1,1,'')
end
goselect id,name,type=dbo.g_getstring(id)
from 表1
insert into tt select 1,'张' union all
select 2,'张' union all
select 3,'王' create table tb (Type varchar(10),ID int)
insert into tb select 'A',1 union all
select 'B',1 union all
select 'B',2 union all
select 'C',3 union all
select 'B',3
gocreate function dbo.fc_str(@id varchar(100))
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+';'+cast(type as varchar(100)) from tb where id=@id
return stuff(@sql,1,1,'')
end
goselect a.id,b.name,dbo.fc_str(a.id) as type from tb a,tt b where a.id=b.id group by a.id,b.name order by a.id
CREATE Procedure sp_FType--定义--With ENCRYPTION
As
Set Nocount On
declare @ID int, @Type varchar(20)
SELECT TABLE2.ID, Type -- 建立临时空表
INTO #table
FROM TABLE2
GROUP BY ID, Type
ORDER BY ID, Type
delete #Table
DECLARE Sale_Curs CURSOR FOR
SELECT ID, Type FROM TABLE2
GROUP BY ID, Type
ORDER BY ID, Type
OPEN Sale_Curs
FETCH Sale_Curs INTO @ID, @Type
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT ID FROM #Table
WHERE ID = @ID --AND Type = @Type
IF @@rowcount > 0
UPDATE #Table SET Type = type + ';' + @Type WHERE ID = @ID AND Type <> @Type
else
Insert into #Table (ID,Type)
values(@ID,@Type)
FETCH Sale_Curs INTO @ID, @Type
END
CLOSE Sale_Curs
DEALLOCATE Sale_Curs
SELECT #Table.ID, #Table.Type, TABLE1.Name
FROM #Table LEFT OUTER JOIN
TABLE1 ON #Table.ID = TABLE1.ID
GOexec sp_FType
SQL Server 2000通过