求动态生成表结构写法

[dalazhi]

有表

id bh mc
001 01 一号
002 02 二号
003 03 三号
n n n号

要求根据此表生成建表语句

一号 二号 三号 n号

[dalazhi]

厉害，高手，可是我还是不怎么懂。
我是要建表语句

[zjcxc]

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)

[47522341]

高手在你下面呢；^_^

[dalazhi]

创建表

CREATE TABLE huoweizl (
hw VARCHAR(11),
hwbh VARCHAR(10),
huowname VARCHAR(20)
)
GO

INSERT INTO [huoweizl] ([hw], [hwbh], [huowname])
VALUES
('HWI00000001', '1001', '一号库')
GO

INSERT INTO [huoweizl] ([hw], [hwbh], [huowname])
VALUES
('HWI00000002', '1002', '二号库')
GO

INSERT INTO [huoweizl] ([hw], [hwbh], [huowname])
VALUES
('HWI00000003', '2001', '三号库')
GO

INSERT INTO [huoweizl] ([hw], [hwbh], [huowname])
VALUES
('HWI00000004', '2002', '四号库')
GO

INSERT INTO [huoweizl] ([hw], [hwbh], [huowname])
VALUES
('HWI00000006', '3001', '五号库')
GO

INSERT INTO [huoweizl] ([hw], [hwbh], [huowname])
VALUES
('HWI00000011', '3002', '六号库')
GO

INSERT INTO [huoweizl] ([hw], [hwbh], [huowname])
VALUES
('HWI00000012', '4001', '七号库')
GO

INSERT 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。但是我不知道怎么才可以使他排序。请高手指点

[OracleRoob]

可以按照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)

[dalazhi]

还有就是我无法用这段语句在存储过程里创建临时表
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' 无效。

高手们，可以解决这个问题吗？

[OracleRoob]

--用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)

[OracleRoob]

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

[OracleRoob]

--用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) --加括号

[dalazhi]

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

[dalazhi]

回 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

[dalazhi]

刚刚错了，现在是正确的语句

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

create table #aa([一号库] varchar(100),[二号库] varchar(100),[三号库] varchar(100),[四号库] varchar(100),[五号库] varchar(100),[六号库] varchar(100),[七号库] varchar(100),[八号库] varchar(100))
服务器: 消息 208，级别 16，状态 1，行 10
对象名 '#aa' 无效。

[软侠]

對於這個問題是無法解決的，因為在使用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

[zhouhaihe]

学习

[OracleRoob]

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

[OracleRoob]

但是全局临时表所有会话可见，即所有的连接共享同一个全局临时表
不推荐使用全局临时表，下面语句创建了局部临时表，并在动态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')

[软侠]

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

[dalazhi]

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

[OracleRoob]

--用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)

[zjcxc]

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)