怎样动态增加列和行

Question

T1 （FDetailID  + FClassID   唯一，FDetailID有规律递增）
FDetailID  FClassID  FItemID
1          1         11
1          3         207
2          3         31
3          1         121
T1_横向展开形式表 （FDetailID  唯一，FDetailCount是T1 FDetailID  相同的计数）
FDetailID  FDetailCount  F1   F3
1          2             11  207
2          1             0    31
3          1             121   0T2 （FDetailID  + FClassID   唯一 FDetailID有规律递增）
FDetailID  FClassID  FItemID
1          1         11
1          3         207
2          3         31
3          3         98
4          1         100
4          2002      3341，要求根据T2，往T1增加T1在T2在没有的行
最终T1数据库变成
T1 
FDetailID  FClassID  FItemID
1          1         11
1          3         207
2          3         31
3          1         121
--------下面是新增的行-------
4          3         98
5          1         100
5          2002      334    2，根据在T1新增的行，在表T1_横向展开形式表 动态增加列和行
最后T1_横向展开形式表变成
T1_横向展开形式表
FDetailID  FDetailCount  F1    F3     F2002
1          2             11    207       0
2          1             0      31       0
3          1             121     0       0
4          1             0      98       0
5          2             100     0     334

Answer 1

LZ你的数据有点不对，下面是按照你的要求，按步骤做的T1 （FDetailID + FClassID 唯一，FDetailID有规律递增）
CREATE TABLE T1( 
FDetailID INT, FClassID INT, FItemID INT)
INSERT T1
SELECT 1 ,1 ,11 UNION ALL
SELECT 1, 3 ,207 UNION ALL
SELECT 2 ,3 ,31 UNION ALL
SELECT 3 ,1 ,121
T1_横向展开形式表 （FDetailID 唯一，FDetailCount是T1 FDetailID 相同的计数）
FDetailID FDetailCount F1 F3
1 2 11 207
2 1 0 31
3 1 121 0
--1
declare @sql varchar(8000)
set @sql = 'select FDetailID,COUNT(FDetailID) AS FDetailCount '
select @sql = @sql + ' , max(case FClassID when ''' + LTRIM(FClassID) + ''' then FItemID else 0 end) [f' + LTRIM(FClassID) + ']'
from (select distinct FClassID from T1) as T
set @sql = @sql + ' from T1 group by FDetailID'
exec(@sql) /*FDetailID   FDetailCount f1          f3          
----------- ------------ ----------- ----------- 
1           2            11          207
2           1            0           31
3           1            121         0（所影响的行数为 3 行）
*/
T2 （FDetailID + FClassID 唯一 FDetailID有规律递增）
CREATE TABLE T2( 
FDetailID INT, FClassID INT, FItemID INT)
INSERT T2
SELECT 1, 1, 11 UNION ALL
SELECT 1, 3, 207 UNION ALL
SELECT 2, 3, 31 UNION ALL
SELECT 3, 3, 98 UNION ALL
SELECT 4, 1, 100 UNION ALL
SELECT 4, 2002, 3341，要求根据T2，往T1增加T1在T2在没有的行
最终T1数据库变成
T1  
FDetailID FClassID FItemID
1 1 11
1 3 207
2 3 31
3 1 121
--------下面是新增的行-------
4 3 98
5 1 100
5 2002 334   INSERT T1 SELECT * FROM T2 WHERE  NOT EXISTS( SELECT 1 FROM T1 T WHERE T2.FDetailID=T.FDetailID AND T2.FClassID=T.FClassID)
SELECT * FROM T1
/*FDetailID   FClassID    FItemID     
----------- ----------- ----------- 
1           1           11
1           3           207
2           3           31
3           1           121
3           3           98
4           1           100
4           2002        334（所影响的行数为 7 行）*/2，根据在T1新增的行，在表T1_横向展开形式表 动态增加列和行
最后T1_横向展开形式表变成
T1_横向展开形式表
FDetailID FDetailCount F1 F3 F2002
1 2 11 207 0
2 1 0 31 0
3 1 121 0 0
4 1 0 98 0
5 2 100 0 334--1
declare @sql varchar(8000)
set @sql = 'select FDetailID,COUNT(FDetailID) AS FDetailCount '
select @sql = @sql + ' , max(case FClassID when ''' + LTRIM(FClassID) + ''' then FItemID else 0 end) [f' + LTRIM(FClassID) + ']'
from (select distinct FClassID from T1) as T
set @sql = @sql + ' from T1 group by FDetailID'
exec(@sql) /*
FDetailID   FDetailCount f1          f3          f2002       
----------- ------------ ----------- ----------- ----------- 
1           2            11          207         0
2           1            0           31          0
3           2            121         98          0
4           2            100         0           334（所影响的行数为 4 行）
*/

Answer 2

T1，T1_横向展开形式表，T2都是已经存在的表。所以要有动态增加列和行的操作，不是要查询结果
T1有手工增加的，也有和T2同步来的，所以最好T1和T2不一定一样。

Answer 3

T2的FDetailID和T1_横向展开形式表不能一一对应，
T1_横向展开形式表的FDetailID 只和T1FDetailID对应

Answer 4

create table a_(FID INT , FEntryID INT, FDetailID INT)
insert a_ select 11, 1, 16
union all select 11, 2, 20create table b_(FDetailID INT, FCount INT, F2 INT, F8 INT, F2033 INT)
insert b_ select 16, 0, 0, 207, 0  
union all select 20, 1, 0, 0, 517--T3:（FInterId +FClassID 唯一）
CREATE TABLE c_(FInterId INT, FClassID INT, FNumber VARCHAR(20))
insert c_ select 207, 8 ,'ABC'  
union all select 207, 11 ,'EEE'
union all select 517, 2033 ,'UUU'DECLARE @sql VARCHAR(1000),@sql1 VARCHAR(1000),@sql2 VARCHAR(1000)SELECT @sql=ISNULL(@sql+',','')+' max(case when ['+name+']>0 then '+REPLACE(NAME,'F','')+' else 0 end) ['+name+']' ,
@sql1=ISNULL(@sql1+'+','')+' ['+NAME+']' 
FROM syscolumns s WHERE id=OBJECT_ID('b_') AND colid>3IF OBJECT_ID('d_') IS NOT NULL DROP TABLE d_
IF OBJECT_ID('e_') IS NOT NULL DROP TABLE e_
IF OBJECT_ID('f_') IS NOT NULL DROP TABLE f_EXEC('select FDetailid,'+@sql+' into d_ from b_ group by FDetailid')
EXEC('select FDetailid,'+@sql1+' col into e_ from b_')
EXEC('select FDetailid,'+@sql1+' col into f_ from d_ ')
EXEC('select aa.Fid,aa.FEntryId,aa.FDetailId,ee.FClassId,ee.Fnumber from a_ aa inner join b_ bb on aa.FDetailId=bb.FDetailId inner join '+
'e_ cc on bb.FdetailId=cc.FdetailId inner join f_ dd on cc.FdetailId=dd.FdetailId '+
' inner join c_ ee on cc.col=ee.FInterId and dd.col=ee.FClassId'
)
/*
Fid         FEntryId    FDetailId   FClassId    Fnumber              
----------- ----------- ----------- ----------- -------------------- 
11          1           16          8           ABC
11          2           20          2033        UUU
*/

Answer 5

5楼回复的是
http://topic.csdn.net/u/20100524/16/0136b36f-d149-44ce-9dfc-ab729d4dbd96.html

Answer 6

算了，重新提需求，简单点，系统管理员方便的话帮我改一下。
新需求如下：
FDetailID FClassID FItemID
4         1         566  
4         3003      889 T2 （FDetailID 唯一，FDetailCount是T1 FDetailID 相同的计数）
FDetailID FDetailCount F1   F3
1         2            11   207
2         1             0   31
3         1            121   0要求根据T1，T2的内容
1，在表T2动态增加列，按上面的例子，应该增加列F3003，因为F1已经存在
2，在表T2动态增加行，行内容来自于T1，增加后T2变成T2 （FDetailID 唯一，FDetailCount是T1 FDetailID 相同的计数）
FDetailID FDetailCount F1   F3    F3003
1         2            11   207   0
2         1             0   31    0
3         1            121   0    0
4         2            566   0    889就解决上述两个问题
谢谢

Answer 7

CREATE TABLE a_(FDetailID INT, FClassID INT, FItemID INT)
INSERT a_ SELECT 4, 1, 566   
union all SELECT 4, 3003, 889  DROP TABLE B_
CREATE TABLE b_(FDetailID INT, FDetailCount INT, F1 INT, F3 INT)
INSERT b_ SELECT 1, 2, 11, 207
UNION ALL SELECT 2, 1, 0, 31
UNION ALL SELECT  3, 1, 121, 0DECLARE @FClassId INT ,@FDetailID INT ,@Count INT 
DECLARE @SQL VARCHAR(1000),@SQL1 VARCHAR(1000),@SQL2 VARCHAR(1000)DECLARE cur CURSOR FOR SELECT FClassId FROM a_ a
WHERE NOT EXISTS(select 1 FROM  syscolumns s WHERE id=OBJECT_ID('b_') AND colid>2 AND NAME='F'+ltrim(a.FClassID))OPEN cur 
FETCH FROM cur INTO @FClassId
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @FClassId
SET @SQL='ALTER TABLE B_ ADD [F'+LTRIM(@FClassId)+'] INT DEFAULT(0) WITH VALUES'
EXEC(@SQL)
FETCH FROM cur INTO @FClassId
END
CLOSE cur 
DEALLOCATE cur DECLARE CUR1 CURSOR FOR SELECT FDetailID,COUNT(1) C FROM A_ A WHERE NOT EXISTS(SELECT 1 FROM B_ WHERE FDetailID=A.FDetailID )  GROUP BY A.FDetailIDOPEN CUR1
FETCH FROM cur1 INTO @FDetailID,@Count
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL1='INSERT B_(FDetailID,FDetailCount) VALUES('+LTRIM(@FDetailID)+','+LTRIM(@Count)+')'
EXEC(@SQL1)
SELECT @SQL2=ISNULL(@SQL2+',','')+'[F'+LTRIM(FClassId)+']='+LTRIM(FItemId) FROM A_ WHERE FDetailID=@FDetailID
SET @SQL1='UPDATE B_ SET '+@SQL2+' WHERE FDetailID='+LTRIM(@FDetailID)
EXEC(@SQL1)
FETCH FROM cur1 INTO @FDetailID,@Count
ENDCLOSE CUR1
DEALLOCATE CUR1 SELECT * FROM B_
--result
/*FDetailID   FDetailCount F1          F3          F3003       
----------- ------------ ----------- ----------- ----------- 
1           2            11          207         0
2           1            0           31          0
3           1            121         0           0
4           2            566         NULL        889
*/

Answer 8

请认真测试，把DROP TABLE B_ 一句删 了

Answer 9

CREATE TABLE a_(FDetailID INT, FClassID INT, FItemID INT)
INSERT a_ SELECT 4, 1, 566   
union all SELECT 4, 3003, 889  
go 
CREATE TABLE b_(FDetailID INT, FDetailCount INT, F1 INT, F3 INT)
INSERT b_ SELECT 1, 2, 11, 207
UNION ALL SELECT 2, 1, 0, 31
UNION ALL SELECT  3, 1, 121, 0
go 
....加上go就行了

Answer 10

DROP TABLE B_ 已经删除了