22,210
社区成员
发帖
与我相关
我的任务
分享
create table t1(a nvarchar(10),b nvarchar(10),c nvarchar(10),d nvarchar(10), id int)
create table t2(a nvarchar(10),e nvarchar(10),f nvarchar(10),id int)
create table t3(b nvarchar(10),c nvarchar(10),g nvarchar(10),h nvarchar(10), id int)
insert t1
select '1','2','3','4',0
insert t2
select '5','6','7',0
insert t3
select '8','9','10','0',0
declare @id int
set @id=0
update t1 set @id=@id+1, id=@id
update t2 set @id=@id+1, id=@id
update t3 set @id=@id+1, id=@id
select t.name as col,object_name(c.id) as tableName
into #
from (
select name
from syscolumns
where object_name(id) in ('t1','t2','t3') and name<>'id'
group by name
having count(*)>1
) t join syscolumns as c on c.name=t.name
where object_name(c.id) in ('t1','t2','t3')
declare @str varchar(8000),@grp int, @str2 varchar(8000),@col varchar(255),@cols varchar(1000)
set @str2=''
set @grp=1
set @cols=''
select @cols=@cols+','+name
from (
select name
from syscolumns
where object_name(id) in ('t1','t2','t3')
group by name
having count(*)=1
) t
select *,grp=(select count(distinct col) from # where col<=t.col)
into #2
from # as t
while exists(select * from #2 where grp=@grp)
begin
set @str=''
select @str=@str+','+tableName+'.'+col,
@col=col
from #2
where grp=@grp
set @str=',COALESCE('+stuff(@str,1,1,'')+') as '+@col
set @str2=@str2+@str
set @grp=@grp+1
end
set @str2=stuff(@str2,1,1,'')
exec('select '+ @str2+@cols+' from t1 full join t2 on t1.id=t2.id full join t3 on t1.id=t3.id')
drop table t1,t2,t3,#,#2
/*
a b c d e f g h
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2 3 4 NULL NULL NULL NULL
5 NULL NULL NULL 6 7 NULL NULL
NULL 8 9 NULL NULL NULL 10 0
*/
USE [PrintingProject]
GO
/****** 对象: Table [dbo].[DH_A_Material] 脚本日期: 12/17/2009 15:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DH_A_Material](
[A_Material_Id] [uniqueidentifier] NOT NULL,
[A_Material_Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[a] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[b] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[c] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[d] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[e] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_DH_A_MATERIAL] PRIMARY KEY CLUSTERED
(
[A_Material_Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [PrintingProject]
GO
/****** 对象: Table [dbo].[DH_M_Material] 脚本日期: 12/17/2009 15:24:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DH_M_Material](
[M_Material_Id] [uniqueidentifier] NOT NULL,
[M_Material_Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[a] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[f] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[g] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_DH_M_MATERIAL] PRIMARY KEY CLUSTERED
(
[M_Material_Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [PrintingProject]
GO
/****** 对象: Table [dbo].[DH_O_Material] 脚本日期: 12/17/2009 15:25:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DH_O_Material](
[O_Material_Id] [uniqueidentifier] NOT NULL,
[O_Material_Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[b] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[c] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[h] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[i] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_DH_O_MATERIAL] PRIMARY KEY CLUSTERED
(
[O_Material_Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
最好给出完整的表结构,测试数据,计算方法和正确结果.