MSSQL中,有位网友email问我的问题,下面是我给出的方案,大家也来看一看吧.(zhuzhichao)

问题描述:
有表Table2,其结构及内容如下:
Table2:

id name1 name2 name3 ……(列有很多)
1 aa bb cc ……
2 ii jj kk ……
3 xx yy zz ……
…………………………………………

有表Table1,其结构如下:
Table1:
id variable

现在想把Table2中的除id以外的字段名(注意,是字段名)填入Table1中的id,
从Table2中提取某一行(先假设是id为1的那一行吧)中与每个字段名对应的内容填入Table1的variable.

以id为1的那行为例,就是要达到如下的效果:
Table1:
id variable
name1 aa
name2 bb
name3 cc
……………….


我的解决方案:

declare @vVariable varchar(2000),@var varchar(2000) --中間值的變量
declare @sql nvarchar(4000) -- 動態sql
declare @insertSql varchar(4000) -- 插入臨時表的sql
set @vVariable = ' '
if object_id('tempdb.dbo.#temp1') is not null
drop table #temp1
select identity(int) id,name into #temp1 from syscolumns where id = object_id('Table2') and name <> 'id' order by colid 

if object_id('tempdb.dbo.#temp2') is null -- 創建臨時表#temp2
create table #temp2
(
id int identity,
   variable varchar(10)
  )
else
  truncate table #temp2

select @vVariable = @vVariable + '+'',''+' + name from syscolumns where id =
object_id('Table2') and name <> 'id' order by colid
set @vVariable = right(@vVariable,len(@vVariable) - 6)
set @sql = 'select @var = '+@vVariable+' from Table2 where id = 1'
exec sp_executesql @sql,N'@var varchar(2000) output',@var output -- 組成以逗號分格的變量@var

set @insertSql='insert into #temp2 values('''+REPLACE(@var,',',''')
insert into #temp2 values(''')+''')'
exec (@insertSql)  -- 根據變量中的逗號分格插入臨時表

insert into Table1(id,variable)
select name,variable from #temp1,#temp2
where #temp1.id = #temp2.id

select * from Table1

59 个解决方案

#1

很好

#2

很好

#3

收藏!!!
如果反过来又怎么实现呢 .....思考中.....

#4

这里也太慢了，可否将论坛改一种格式，加快速度，不然老半天也出不来。

#5

竹兄：

就是利用你出的趣味SQL问题上的方法可以实现

而且

#6

竹兄：

就是利用你出的趣味SQL问题上的方法可以实现

而且你用的也是这个方法：）

#7

不錯,這里也用到了那樣的技巧.
不過,這段程序的技巧更多.
如能將每個步驟搞清的話,將對你很有幫助.

LiaoCheng(Flower_梦里看花!!):
如果要反過來,請看
http://www.itpub.net/showthread.php?s=&threadid=11308
有很詳細的說明.

#8

生成pivot table的问题，论坛里总有人问。
竹兄这个是地道的解决办法，谢了，收藏。不过建议针对所有记录应该再
把循环处理的语句加进去，再写一个由table1生成table2的过程（改用table变量写吧，大家多一点学习内容）。这样如再有类似提问，用此贴就可以解决了。

#9

生成pivot table的问题，论坛里总有人问。
竹兄这个是地道的解决办法，谢了，收藏。不过建议针对所有记录应该再
把循环处理的语句加进去，再写一个由table1生成table2的过程（改用table变量写吧，大家多一点学习内容）。这样如再有类似提问，用此贴就可以解决了。

#10

斑竹，干脆把如下格式的也写了：

Table1:
id    var1  var2   var3 ..... (这里的1，2，3.....是ID的值)
name1 aa    ii     xx  ……
name2 bb    jj     yy  ……
name3 cc    kk     zz  ……
……………….

#11

to Haiwer(海阔天空):

好,马上就写.

#12

按照Haiwer(海阔天空)的格式也寫好了.


declare @vVariable varchar(2000),@var varchar(2000) --中間值的變量
declare @sql nvarchar(4000) -- 動態sql
declare @insertSql varchar(4000) -- 插入臨時表的sql
declare @alterSql varchar(100) -- 修改臨時表#temp1的sql
declare @updateSql varchar(200) -- 更新臨時表#temp1的數據sql
declare @i int,@count int -- Table2的紀錄數
select @count = count(1) from Table2;
if @count = 0
begin
print 'No row'
return
end
if @count > 254
begin
print 'Too many rows'
return
end
set @vVariable = ' '
select @vVariable = @vVariable + '+'',''+' + name from syscolumns where id =
object_id('Table2') and name <> 'id' order by colid
set @vVariable = right(@vVariable,len(@vVariable) - 6)

if object_id('tempdb.dbo.#temp1') is not null
drop table #temp1
select identity(int) id,name into #temp1 from syscolumns where id = object_id('Table2') and name <> 'id' order by colid 

set @i = 1
while (@i <= @count)
begin
if object_id('tempdb.dbo.#temp2') is null -- 創建臨時表#temp2
create table #temp2
(
id int identity,
  variable varchar(10)
)
else
truncate table #temp2

set @sql = 'select @var = '+@vVariable+' from Table2 where id = ' + convert(varchar(3),@i)
exec sp_executesql @sql,N'@var varchar(2000) output',@var output -- 組成以逗號分格的變量@var

set @insertSql='insert into #temp2 values('''+REPLACE(@var,',',''')
insert into #temp2 values(''')+''')'
exec (@insertSql)  -- 根據變量中的逗號分格插入臨時表

set @alterSql = 'alter table #temp1 add var' + convert(varchar(3),@i) + ' varchar(10)'
exec (@alterSql)   -- 增加臨時表的字段

set @updateSql = 'update #temp1 set var' + convert(varchar(3),@i) + ' = variable from #temp2 '
+'where #temp1.id = #temp2.id'
exec (@updateSql)   -- 更新臨時表的數據

set @i = @i + 1
end
alter table #temp1 drop column id
select * from #temp1

#13

收藏先！

#14

想不到为了我的一个问，大哥竹之草还发了个贴，哈哈，谢谢大哥。
我的问题是不是也让大家得到了一些启示呢。

#15

不错不错,长了知识又捡分

#16

我的问题今天在竹之草大哥的帮助下解决了，而且我已经能基本掌握程序实现的思想了，经过我的改良程序已经很稳定的运行了。我的任务过了一半了。哈哈，我太高兴了。

#17

斑竹功德无量，应该放到精华区！

#18

斑竹高明，收藏并学习。

#19

这种问题真的很有实用价值，受益不浅!!!

#20

学习！

#21

竹兄：
解决方案很好，技巧不少。不过，有个小小的疑问：
如果表Table2的列非常多，那么需要@sql、@insertSql这两个用于构造动态SQL的变量描述的内容就会很长--但是，这两个变量的长度终归是有限的，也就是说，有可能会造成变量的长度不够。好了，就我所知，SQLServer的处理是简单地把超出部分斩断：显然，此时
exec sp_executesql @sql,N'@var varchar(2000) output',@var output -- 組成以逗號分格的變量@var
和
exec (@insertSql)  -- 根據變量中的逗號分格插入臨時表
这两句就会出错！而且，实际上问题是@sql、@insertSql中存在语法错（也就是说，他们的值不是我们想象的值），系统也将如此报告错误信息，而这两条将执行的SQL语句在我们的proc中并没有出现，将对调试和维护带来很大的疑惑和困难。

#22

study

#23

这种问题我在实际中也遇到过，有时根据需要将用户报表的列转化为行的形式在实际表中存储，有时根据需要将用户报表的行转化为列的形式在实际表中存储；前者用户可以灵活调整数据项，但在数据量大时易造成数据冗余，后者有表结构固定的缺陷。
看了斑竹的方法受益非浅！

#24

学习

#25

收

#26

good,I am studying...

#27

收藏

#28

把她翻译成oralce版就好了, 收藏先！

#29

还没看，大家说好，就收藏先~

#30

好好学习,天天向上.

#31

置頂.

#32

收藏先。。。。

#33

不错....

#34

绝妙的文字游戏！！

#35

不错，值得借鉴。

#36

占个位置

#37

这一句
set @insertSql='insert into #temp2 values('''+REPLACE(@var,',',''')
insert into #temp2 values(''')+''')'
构思独到！
不错,不错！

如果系统里过多使用这种方法，加上用户使用频率再高一些，服务器会很很累的....
我觉得在客户端生成pivot table会更好一些，大大减少了服务器的开销。

#38

不太懂，先研究研究

#39

zhuzhichao,好方法，能想别人不能想!
奇妙的字符串连接方法，oracle却不能这么玩

#40

收藏

#41

有种转置矩阵表的技巧,在数据库上很有用..
可以不用这些存贮过程............
....可有人感兴趣吗?

#42

没想到竹大哥给我回答的一个问题能让这么多人引起共鸣，哈哈。

#43

介绍给大家一个好东东: http://www27.brinkster.com/zlb1978

    科旭即时通是一个优秀的即时通讯软件，该软件采用分布式结构，包括服务器和客户端两部分，适用于局域网，广域网，和WWW三种网络体系，用户可以自主设置服务器和通讯端口，可方便迅速地在单位内部建立起多个独立的聊天体系。  

     

#44

xuexi !

#45

不知道执行过没有，反正我无法执行
第一，
select @vVariable = @vVariable + '+'',''+' + name from syscolumns where id =
object_id('Table2') and name <> 'id' order by colid
好象是以+，+分隔的，并不是，

另外，set @sql = 'select @var = '+@vVariable+' from Table2 where id = 1'
这句SQL好象返回的是
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

#46

我一点也看不懂
不过还是收藏一下
呵呵

#47

set @insertSql='insert into #temp2 values('''+REPLACE(@var,',',''')
insert into #temp2 values(''')+''')'

这句  什么 意思？

#48

关注

#49

开始是简体汉字，后来变成是繁体？
不明白，思考中……

#50

study