使这种查询动态化

发布于 2024-12-04 09:44:57 字数 539 浏览 0 评论 0原文

我正在执行一个查询来创建一个在 SQL 中包含两个元素的表

SELECT [att1]
      ,[att2]
      ,[att3]
      ,[att4]
      ,[att5]
      ,[att6]
      ,[att7]
      ,[att8]
      ,[att9]
      ,[att10]
      ,att11 = att1,
       att12 = att2,
       att13 = att3,
       att14 = att4,
       att15 = att5,
       att16 = att6,
       att17 = att7,
       att18 = att8,
       att19 = att9,
       att20 = att10
INTO Table_20
FROM Table_10;

，使此动态化的最佳方法是什么？如果我有一个包含 30 atts 的表，请使用 30 创建一个表* 2 atts（双倍大小）？

原文

I am making a query to create a table which has twice elements in SQL

SELECT [att1]
      ,[att2]
      ,[att3]
      ,[att4]
      ,[att5]
      ,[att6]
      ,[att7]
      ,[att8]
      ,[att9]
      ,[att10]
      ,att11 = att1,
       att12 = att2,
       att13 = att3,
       att14 = att4,
       att15 = att5,
       att16 = att6,
       att17 = att7,
       att18 = att8,
       att19 = att9,
       att20 = att10
INTO Table_20
FROM Table_10;

What would be the best way to make this dynamic and if I have a table with 30 atts, make a table with 30 * 2 atts (double the size)?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

明月夜 2024-12-11 09:44:57

DECLARE 
    @table NVARCHAR(512) = N'dbo.Table_10';
    @sql   NVARCHAR(MAX) = N'',
    @c     INT;

SELECT @sql += N',' + name 
    FROM sys.columns 
    WHERE [object_id] = OBJECT_ID(@table) 
    AND name LIKE 'att%';

SELECT @c = @@ROWCOUNT;

SELECT @sql += N',att' + CONVERT(VARCHAR(12), 
      @c + CONVERT(INT, REPLACE(name, 'att', '')))
      + ' = ' + name 
    FROM sys.columns  
    WHERE [object_id] = OBJECT_ID(@table) 
    AND name LIKE 'att%';

SELECT @sql = N'SELECT ' + STUFF(@sql, 1, 1, '') 
    + ' INTO dbo.Table_20 FROM ' + @table + ';';

PRINT @sql;
-- EXEC sp_executesql @sql;

DECLARE 
    @table NVARCHAR(512) = N'dbo.Table_10';
    @sql   NVARCHAR(MAX) = N'',
    @c     INT;

SELECT @sql += N',' + name 
    FROM sys.columns 
    WHERE [object_id] = OBJECT_ID(@table) 
    AND name LIKE 'att%';

SELECT @c = @@ROWCOUNT;

SELECT @sql += N',att' + CONVERT(VARCHAR(12), 
      @c + CONVERT(INT, REPLACE(name, 'att', '')))
      + ' = ' + name 
    FROM sys.columns  
    WHERE [object_id] = OBJECT_ID(@table) 
    AND name LIKE 'att%';

SELECT @sql = N'SELECT ' + STUFF(@sql, 1, 1, '') 
    + ' INTO dbo.Table_20 FROM ' + @table + ';';

PRINT @sql;
-- EXEC sp_executesql @sql;

回复收藏 0 原文

~没有更多了~