SQL 表關聯

[shentanyi]

-->表一
if object_id('tb') is not null drop table tb
go
create table tb(ID int,TableName nvarchar(50),F1 nvarchar(50),F2 nvarchar(50),F3 nvarchar(50))
insert tb
select 1,'S1','a','b','c' union all
select 1,'S1','a1','b1','c1'

--select * from tb

-->表二
if object_id('tb2') is not null drop table tb2
go
create table tb2(ID int,TableName nvarchar(50),MapFieldName nvarchar(50),FieldName nvarchar(50))
insert tb2
select 1,'S1','F1','Name' union all
select 1,'S1','F2','Old' union all
select 1,'S1','F3','Address'

--select * from tb2
--讓tb 的F1,F2,F3換成tb2中的F1對應[Name],F2對應Old,F3對應Address
--如下表所示
--最好不要使用遊標
[Name] Old [Address]
a      b       c
a1     b1      c1

[lifenglijun]

关注。如何换列名

[--小F--]

感觉是行转列啊

[nianran520]

。。。换列名？

[dawugui]

你的数据如何匹配?

ID          TableName                                          F1                                                 F2                                                 F3                                                 
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
1           S1                                                 a                                                  b                                                  c
1           S1                                                 a1                                                 b1                                                 c1

（所影响的行数为 2 行）

ID          TableName                                          MapFieldName                                       FieldName                                          
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
1           S1                                                 F1                                                 Name
1           S1                                                 F2                                                 Old
1           S1                                                 F3                                                 Address

（所影响的行数为 3 行）

谁匹配谁?

[shentanyi]

條件兩個表的ID與TableName相等時

[shentanyi]

就是換列名

[shentanyi]

set @sqlFileST='select '
	--	set @Import_MarkID='admin20090917120438660'
	--	set @Import_TableName='Sheet1$'
		set @sqlValueST=' from dbo.tb '
		
		--print @sqlFileST
		--print @sqlValueST
		--print @Import_TableName
declare Bind_Cursor cursor for
		select MapFieldName,FieldName from dbo.tb2  order by FieldOrder
			open Bind_Cursor
			fetch next from Bind_Cursor into @MapField,@ItemNo
			while @@FETCH_STATUS=0
			begin
				set @FieldSourse=@MapField
				set @sqlFileST=@sqlFileST+@FieldSourse+' as '''+@ItemNo+''','
				fetch next from Bind_Cursor into @MapField,@ItemNo
			end
			close Bind_Cursor
			deallocate Bind_Cursor
			set @sql=left(@sqlFileST,len(@sqlFileST)-1)+@sqlValueST	
		exec (@sql)

這是我用遊標實現的情況

[dawugui]

[Quote=引用 4 楼 shentanyi 的回复:]
條件兩個表的ID與TableName相等時
[/Quote]
那你要么是:
a b c
要么是:
a1 b1 c1
如何两个都要呢?

[shentanyi]

wu gui

其實是簡單的換列名

[lkskldkldsds]

if object_id('tb') is not null drop table tb
go
create table tb(ID int,TableName nvarchar(50),F1 nvarchar(50),F2 nvarchar(50),F3 nvarchar(50))
insert tb
select 1,'S1','a','b','c' union all
select 1,'S1','a1','b1','c1'



if object_id('tb2') is not null drop table tb2
go
create table tb2(ID int,TableName nvarchar(50),MapFieldName nvarchar(50),FieldName nvarchar(50))
insert tb2
select 1,'S1','F1','Name' union all
select 1,'S1','F2','Old' union all
select 1,'S1','F3','Address'



select F1 as 'Name',F2 as 'Old',F3 as 'Address' from dbo.tb 


Name     Old     Address   
------  ------ ---------                                         
a         b        c
a1        b1       c1

[rw550zt]

关注。如何换列名