SQL怎样建动态表
现有表 a
no 方式 金额
1 800 100.00
2 801 20.00
1 900 50.00
表 b
code name
800 现金
801 支票
900 金卡
表a中的方式对应表b中的code, 这个项目不是固定的有可能还有 901,902,903，。。。
怎样建立一个表结构形成
no 现金 支票 金卡 。。。
1 100.00 0 50.00
2 0 20.00 0
不是说用行转列查询，是建一个实表 相当于用存储过程建表但是字段是变量 这该如何实现 SQL 动态建表??实表
[解决办法]
IF OBJECT_ID('tempdb..#tb') IS NOT NULL
DROP TABLE #tb;
with a (no,code,amount) as
(
select 1,800,100.00 union all
select 2,801,20.00 union all
select 1,900,50.00
)
,b (code,name) as
(
select 800,'现金' union all
select 801,'支票' union all
select 900,'金卡'
)
select a.*,b.name
into #tb
from a
inner join b on a.code=b.code
declare @sql varchar(max),@sql2 varchar(max)
select @sql = isnull(@sql + '],[' , '') + name from (select distinct code,name from #tb) a
set @sql = '[' + @sql + ']'
select @sql2 = isnull(@sql2 + ',' , ',') + 'isnull('+name+',0) '+name from (select distinct code,name from #tb) a
set @sql='select no'+@sql2+' from (select no,name,amount from #tb) a pivot (max(amount) for name in (' + @sql + ')) b'
exec (@sql)
[解决办法]
DECLARE @sql VARCHAR(8000)
SELECT * INTO #aaa FROM t1 AS a INNER JOIN t2 AS b ON a.fangshi=b.code
--SELECT * FROM aaa
SET @sql='select id'
SELECT @sql=@sql+',max(case when name='''+name+''' then jine else 0.00 end) as ['+name+']'
FROM (SELECT DISTINCT name FROM t2) AS abc
SET @sql=@sql+' into t3 from #aaa group by id'
PRINT @sql
EXEC (@sql)
DROP TABLE #aaa
select * from t3 --t3就是新创建的表
