求动态生成表结构写法

Question

Select * into tmpABC from (Select 1 as col1,N'名字1' as col2, 'a' as col3
union select 2,N'名字1','a'
union select 3,N'名字1','b'
union select 4,N'名字1','c') adeclare @StrCol3 nvarchar(1000)
declare @StrSql nvarchar(1000)
declare @Col2 nvarchar(10)
declare @Col3 nvarchar(10)
declare @Counts nvarchar(10)
set @StrCol3=''
set @StrSql=''
declare cursor_out cursor local for Select col2,col3,count(*) as Counts from tmpABC group by col2,col3
open cursor_out
fetch next from cursor_out into @Col2,@Col3,@Counts
while @@fetch_status=0
    begin       
set @StrCol3=@StrCol3 + ' ,(Select count(*) from tmpABC where Col3= ''' + @Col3 + ''' and Col2=N''' + @Col2 +''') as ' + @Col3
        fetch next from cursor_out into @Col2,@Col3,@Counts
    end
CLOSE cursor_out
DEALLOCATE cursor_out
set @StrSql='Select Distinct col2' + @StrCol3 + ' from tmpABC'
exec(@StrSql)drop table tmpABC

Answer 1

declare @s nvarchar(4000)
set @s='create table 表名('
select distinct @s=@s+quotename(mc)+' varchar(100),'   -- 这里指定字段类型
from 表
set @s=left(@s,len(@s)-1)+')'
print(@s)

Answer 2

邹建大哥，运行你的语句后发现，加distinct就只能建立一个字段，不加的话又可能出现重复的值。请大哥指点。
不过你很厉害,原来认为肯定要用游标才可以实现这个功能。
你这么简单就搞掂，佩服佩服

Answer 3

--用Group by
declare @s nvarchar(4000)
set @s='create table 表名('
select @s=@s+quotename(mc)+' varchar(100),'   -- 这里指定字段类型
from 表
group by mc
set @s=left(@s,len(@s)-1)+')'
print(@s)

Answer 4

declare @s nvarchar(4000)
set @s='create table 表名('
select @s=@s+quotename(mc)+' varchar(100),'   -- 这里指定字段类型
from(select distinct mc from 表)a
set @s=left(@s,len(@s)-1)+')'
print(@s)

Answer 5

创建表CREATE TABLE huoweizl (
  hw VARCHAR(11),
  hwbh VARCHAR(10),
  huowname VARCHAR(20)
)
GOINSERT INTO [huoweizl] ([hw], [hwbh], [huowname])
VALUES 
  ('HWI00000001', '1001', '一号库')
GOINSERT INTO [huoweizl] ([hw], [hwbh], [huowname])
VALUES 
  ('HWI00000002', '1002', '二号库')
GOINSERT INTO [huoweizl] ([hw], [hwbh], [huowname])
VALUES 
  ('HWI00000003', '2001', '三号库')
GOINSERT INTO [huoweizl] ([hw], [hwbh], [huowname])
VALUES 
  ('HWI00000004', '2002', '四号库')
GOINSERT INTO [huoweizl] ([hw], [hwbh], [huowname])
VALUES 
  ('HWI00000006', '3001', '五号库')
GOINSERT INTO [huoweizl] ([hw], [hwbh], [huowname])
VALUES 
  ('HWI00000011', '3002', '六号库')
GOINSERT INTO [huoweizl] ([hw], [hwbh], [huowname])
VALUES 
  ('HWI00000012', '4001', '七号库')
GOINSERT INTO [huoweizl] ([hw], [hwbh], [huowname])
VALUES 
  ('HWI00000013', '4002', '八号库')
GO然后执行declare @s nvarchar(4000)
set @s='create table 表名('
select @s=@s+quotename(rtrim(huowname))+' varchar(100),'   -- 这里指定字段类型
from(select distinct huowname from huoweizl)a
set @s=left(@s,len(@s)-1)+')'
print(@s)
得出create table 表名([八号库] varchar(100),[二号库] varchar(100),[六号库] varchar(100),[七号库] varchar(100),[三号库] varchar(100),[四号库] varchar(100),[五号库] varchar(100),[一号库] varchar(100))这里有一个问题就是我没有办法按货位名称排序，这个表里还有一个字段可以排序hwbh。但是我不知道怎么才可以使他排序。请高手指点

Answer 6

可以按照ID顺序进行创建--用Group by
declare @s nvarchar(4000)
set @s='create table 表名('
select @s=@s+quotename(mc)+' varchar(100),'   -- 这里指定字段类型
from 表
group by mc,id
order by id                                   --按ID排序
set @s=left(@s,len(@s)-1)+')'
print(@s)

Answer 7

还有就是我无法用这段语句在存储过程里创建临时表
declare @s nvarchar(4000)
set @s='create table #aa('
select  @s=@s+quotename(rtrim(huowname))+' varchar(100),'   -- 这里指定字段类型
from huoweizl 
group by huowname
set @s=left(@s,len(@s)-1)+')'
print(@s)
exec @s
select #aa错误信息
服务器: 消息 207，级别 16，状态 3，行 9
列名 '#aa' 无效。高手们，可以解决这个问题吗？

Answer 8

--用Group by
declare @s nvarchar(4000)
set @s='create table 表名('
select @s=@s+quotename(huowname)+' varchar(100),'   -- 这里指定字段类型
from huoweizl
group by huowname,id
order by id --按ID排序
set @s=left(@s,len(@s)-1)+')'
print(@s)

Answer 9

declare @s nvarchar(4000)
set @s='create table #aa('
select  @s=@s+quotename(rtrim(huowname))+' varchar(100),'   -- 这里指定字段类型
from huoweizl 
group by huowname
set @s=left(@s,len(@s)-1)+')'
print(@s)
exec (@s)                    ---要加括号
select #aa

Answer 10

--用Group by，这个语句按照ID排序
declare @s nvarchar(4000)
set @s='create table 表名('
select @s=@s+quotename(huowname)+' varchar(100),'   -- 这里指定字段类型
from huoweizl
group by huowname,id
order by id --按ID排序
set @s=left(@s,len(@s)-1)+')'
print(@s)exec (@s)                                                     --加括号

Answer 11

回复wangtiecheng(cappuccino) ( ) 信誉：100 
用了你的语句，头也不痛了，腿也不酸了。感谢

Answer 12

回 wangtiecheng(cappuccino) ( ) 信誉：100 declare @s nvarchar(4000)
set @s='create table #aa('
select  @s=@s+quotename(rtrim(huowname))+' varchar(100),'   -- 这里指定字段类型
from huoweizl 
group by huowname,hw
order by hw
set @s=left(@s,len(@s)-1)+')'
print(@s)
exec (@s)
select #aa
还是提示没有表＃aa

Answer 13

刚刚错了，现在是正确的语句declare @s nvarchar(4000)
set @s='create table #aa('
select  @s=@s+quotename(rtrim(huowname))+' varchar(100),'   -- 这里指定字段类型
from huoweizl 
group by huowname,hw
order by hw
set @s=left(@s,len(@s)-1)+')'
print(@s)
exec (@s)
select * from #aacreate table #aa([一号库] varchar(100),[二号库] varchar(100),[三号库] varchar(100),[四号库] varchar(100),[五号库] varchar(100),[六号库] varchar(100),[七号库] varchar(100),[八号库] varchar(100))
服务器: 消息 208，级别 16，状态 1，行 10
对象名 '#aa' 无效。

Answer 14

對於這個問題是無法解決的，因為在使用exec('動態字串')時，SQL SERVER會自動把動態字串中所有的操作作為一個過程對待，而在過程中產生的臨時表(它也是一種會話臨時表)在過程結束時會被自動清除的，所以就出現了：
服务器: 消息 208，级别 16，状态 1，行 10
对象名 '#aa' 无效。
如果要引用#aa，你隻有在動態字串中引用，在動態字串執行完後，在外部引用必定會出錯的，所以如果要想在動態字串執行完後在外部引用動態字串中創建的臨時表，唯一的方法就是在動態字串中創建全局臨時表，即：
declare @s nvarchar(4000)
set @s='create table ##aa('
select  @s=@s+quotename(rtrim(huowname))+' varchar(100),'   -- 这里指定字段类型
from huoweizl 
group by huowname,hw
order by hw
set @s=left(@s,len(@s)-1)+')'
print(@s)
exec (@s)
select * from ##aa

Answer 15

zjp8310(z一阵风z)说的没错，因为动态SQL语句中创建的是局部临时表，在执行完动态SQL串后系统就自动清除了。
可以创建全局临时表，即加两个#，如：##aa
这样在执行完动态SQL语句后，仍旧可以在当前连接中使用##aa，一旦连接结束，全局临时表也会被系统自动清除。
如：declare @s nvarchar(4000)
set @s='create table ##aa('
select  @s=@s+quotename(rtrim(huowname))+' varchar(100),'   -- 这里指定字段类型
from huoweizl 
group by huowname,hw
order by hw
set @s=left(@s,len(@s)-1)+')'
print(@s)
exec (@s)select * from ##aa

Answer 16

但是全局临时表所有会话可见，即所有的连接共享同一个全局临时表
不推荐使用全局临时表，下面语句创建了局部临时表，并在动态SQL语句执行select返回记录集declare @s nvarchar(4000)
set @s='create table #aa('
select  @s=@s+quotename(rtrim(huowname))+' varchar(100),'   -- 这里指定字段类型
from huoweizl 
group by huowname,hw
order by hw
set @s=left(@s,len(@s)-1)+')'
print(@s)
exec (@s + ' select * from #aa')

Answer 17

我說過，如果在動態字串內引用動態字串創建的臨時表是可以的，就象樓上的說法一樣(不過樓上給的代碼中最後一句有個錯誤，這我就不指出了)，但是要是執行動態字串是嵌在一個存儲過程中，而此存儲過程又要用到執行動態字串時產生的臨時表的話，這該如何是好？顯然唯一的辦法隻有創建##aa表，存儲過程執行完後再DROP掉！