从现有表中为特定列的每个不同值创建一个新表，并使用此不同值命名新表 [英] Create a new table from an existing table for each distinct value of a particular column and name the new table with this distinct value

问题描述

我有一个关于SQL Server数据库的表，我需要为特定列的每个唯一值创建不同的表。



这是对现有的描述table -

 Existing_Table 
 Column_1 Column_2 Column_3 
 A XYZ Group1 
 B ABC Group2 
 C CBA Group1 
 D PQR Group3 
 E XXX Group2 
 F ABC123 Group1 

因此我需要创建一个新表的唯一值是 -

 UNIQUE Column_3 
 Group1 
 Group2 
 Group3 

现在新创建的表格预期看起来像 -

 New_Table_Group1 
 Column_1 Column_2 
 A XYZ 
 C CBA 
 F ABC123 

 New_Table_Group2 
 Column_1 Column_2 
 B ABC 
 E XXX 

 New_Table_Group3 
 Column_1 Colu mn_2 
 D PQR 

这里表的名称将基于创建表的唯一值。



无法达到此目的。有人可以帮助达到预期的效果吗？非常感谢任何帮助。



我尝试过：



我试过下面的代码 -

 DECLARE @i int 
 DECLARE @numrows int 
 DECLARE @indexcount int 
 DECLARE @group VARCHAR（200）
 DECLARE @temp_table表（
 IDX SMALLINT主键IDENTITY（1,1），集团为varchar（200）
）
 
  - 填充组表
 INSERT @temp_table 
 SELECT distinct Column_3 FROM Existing_Table 
 
  - 枚举表
 SET @i = 1 
 SET @numrows =（SELECT COUNT（*）FROM @temp_table）
 SET @indexcount =（SELECT MAX（idx）FROM @temp_table）
 IF @numrows> 0 
 WHILE（@ i< = @indexcount）
 BEGIN 
  - 获取下一个Group主键
 SET @Group =（SELECT Group FROM @temp_table WHERE idx = @一世）; 
 IF OBJECT_ID（'dbo.New_Table_ @ Group'，'U'）IS NOT NULL 
 DROP TABLE dbo.New_Table_@Group; 
从Existing_Table中选择Column_1，Column_2 
到New_Table_ @ Group 
，其中Column_3 = @Group; 
  - 下一个Column_3组值的增量计数器
 SET @i = @i + 1 
 END 

这会失败，因为这只会按名称New_Table_ @ Group本身创建一个新表，并且不会替换预期列中的预期唯一值，因此不会为预期列的每个唯一值创建新表。

解决方案

您需要使用动态sql才能在表名中插入正确的组名。

Eg（未经测试）

  DECLARE   @ i  < span class =code-keyword> int  
  DECLARE   @ numrows  < span class =code-keyword> int  
  DECLARE   @ indexcount  < span class =code-keyword> int  
  DECLARE   @Group   varchar （ 200 ）
  DECLARE   @ temp_table   TABLE （
 idx  smallint  主要 键  IDENTITY （ 1 ， 1 ），[组]  varchar （ 200 ）
）
 
   -   填充组表 
  INSERT   @ temp_table  
  SELECT   distinct  Column_3  FROM  #te st   -   在此子表名称 
 
   -   枚举表 
  SET   @ i  =  1  
  SET   @ numrows  =（ SELECT  COUNT（*） FROM  < span class =code-sdkkeyword> @ temp_table ）
  SET   @ indexcount  =（ SELECT  MAX（idx） FROM   @ temp_table ）
  IF   @ numrows >  0  
  WHILE （ @ i < =  @ indexcount ）
  BEGIN  
   -   获取下一个组主键 
  SET  < span class =code-sdkkeyword> @ Group  =（ SELECT  [ Group ] < span class =code-keyword> FROM   @ temp_table   WHERE  idx =  @我）; 
 
  声明  @sql   nvarchar （max）
 声明  @ drop   nvarchar （max）
 
  set   @ drop  = '  IF OBJECT_ID（''dbo.New_Table _' +  @Group  + ' ''，''U''）IS NOT NULL DROP TABLE dbo。 NEW_TABLE _' + <跨度类= 代码sdkkeyword> @组 
 <跨度类= 代码关键字> EXECUTE  sp_executesql的<跨度类=代码 - sdkkeyword> @ drop  
 
  set   @ sql  = < span class =code-string>'  SELECT Column_1，Column_2 INTO New_Table _' +  @Group  + '  FROM #test WHERE Column_3 =''' +  @ Group  + ' ''' 
  EXECUTE  sp_executesql  @sql   
 
   -   下一个Column_3组值的增量计数器 
  SET   @ i  =  @ i  +  1  
  END  

另请注意在列名称周围使用方括号<$表@temp_table中的c $ c> Group （以及使用它的所有地方）。这是因为Group是保留字，不应该用作列名。只有在用方括号括起来时才能用作列名 - [组]



为了其他读者的利益，请查看我对OP的评论。更好的方法是创建其他应用程序使用的视图。可以使用视图来实现描述，而无需复制数据。对于调用应用程序，查询视图和查询表之间应该没有明显的区别。

上面提供的解决方案可以更改为创建视图而不是表，关键是sql语句必须是动态的。

我使用了光标。



这是我正在使用的代码 -

 DECLARE @Group varchar（200）; 
 DECLARE @CreateTableScript nvarchar（MAX）; 
 
 DECLARE基光标
选择
 Column_3 AS [组] 
，N'IF OBJECT_ID（N''dbo。 + QUOTENAME（ 'New_Table_ LOCAL FAST_FORWARD' + Column_3）+'''，''U''）IS NOT NULL 
 DROP TABLE dbo。'+ QUOTENAME（'New_Table_'+ Column_3）+'; 
选择COLUMN_1，COLUMN_2 
分成DBO+ QUOTENAME（ 'New_Table_' + Column_3）+ '从Existing_Table 
其中Column_3 = @group;' AS CreateTableScript 
 FROM（SELECT DISTINCT Column_3 FROM Existing_Table）AS组; 
 
 OPEN群组; 
 WHILE 1 = 1 
 BEGIN 
 FETCH NEXT FROM groups INTO @Group，@ CreateTableScript; 
 IF @@ FETCH_STATUS = -1 BREAK; 
 PRINT @CreateTableScript; 
 EXEC sp_executesql 
 @CreateTableScript 
，N'@ Group varchar（200）'
，@ Group = @Group; 
 END; 
关闭群组; 
 DEALLOCATE组; 
 GO 

感谢@digimanus和@ CHill60帮助解决这个问题让我学到了很多好东西。

I have a table on SQL Server database from which I need to create different tables for each unique value of a particular column.

Here's the depiction of the existing table -

Existing_Table		
Column_1	Column_2	Column_3
A	        XYZ	        Group1
B	        ABC	        Group2
C	        CBA	        Group1
D	        PQR	        Group3
E	        XXX	        Group2
F	        ABC123	        Group1

So the unique values for which I will need to create a new table will be -

UNIQUE Column_3
Group1
Group2
Group3

Now the newly created tables expected to look like-

New_Table_Group1	
Column_1	Column_2
A	        XYZ
C	        CBA
F	        ABC123

New_Table_Group2	
Column_1	Column_2
B	        ABC
E	        XXX

New_Table_Group3	
Column_1	Column_2
D	        PQR

Here the name of the table will be based on the unique value based on which the table is created.

Couldn't achieve this. Can someone please help achieve the expected result? Any help is much appreciated.

What I have tried:

I have tried the below code-

DECLARE @i int 
DECLARE @numrows int
DECLARE @indexcount int
DECLARE @Group varchar(200)
DECLARE @temp_table TABLE (
    idx smallint Primary Key IDENTITY(1,1), Group varchar(200)
    )

-- populate group table
INSERT @temp_table
SELECT distinct Column_3 FROM Existing_Table

-- enumerate the table
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @temp_table)
SET @indexcount = (SELECT MAX(idx) FROM @temp_table) 
IF @numrows > 0
    WHILE (@i <= @indexcount)
    BEGIN
      -- get the next Group primary key
        SET @Group = (SELECT Group FROM @temp_table WHERE idx = @i);
        IF OBJECT_ID('dbo.New_Table_@Group', 'U') IS NOT NULL 
            DROP TABLE dbo.New_Table_@Group;
        select Column_1, Column_2  
        into New_Table_@Group
        from Existing_Table where Column_3 = @Group;
        -- increment counter for next Column_3 group value
        SET @i = @i + 1
    END

This fails because this only creates a single new table by name 'New_Table_@Group' itself and doesn't substitute the expected unique value from the intended column and hence doesn't create new table for each unique value of the intended column.

解决方案

You need to use dynamic sql in order to insert the correct group name into the table name(s).
E.g (untested)

DECLARE @i int 
DECLARE @numrows int
DECLARE @indexcount int
DECLARE @Group varchar(200)
DECLARE @temp_table TABLE (
    idx smallint Primary Key IDENTITY(1,1), [Group] varchar(200)
    )

-- populate group table
INSERT @temp_table
SELECT distinct Column_3 FROM #test		-- sub your table name here

-- enumerate the table
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @temp_table)
SET @indexcount = (SELECT MAX(idx) FROM @temp_table) 
IF @numrows > 0
    WHILE (@i <= @indexcount)
    BEGIN
      -- get the next Group primary key
        SET @Group = (SELECT [Group] FROM @temp_table WHERE idx = @i);

		declare @sql nvarchar(max)
		declare @drop nvarchar(max)

		set @drop = 'IF OBJECT_ID(''dbo.New_Table_' + @Group + ''', ''U'') IS NOT NULL DROP TABLE dbo.New_Table_' + @Group
		EXECUTE sp_executesql  @drop

		set @sql = 'SELECT Column_1, Column_2 INTO New_Table_' + @Group + ' FROM #test WHERE Column_3 = ''' + @Group + ''''
		EXECUTE sp_executesql  @sql

        -- increment counter for next Column_3 group value
        SET @i = @i + 1
    END

Also note the use of square brackets around the column name Group in table @temp_table (and everywhere it is used). This is because Group is a reserved word and should not be used as a column name. It can be used as a column name only if surrounded by square brackets - [Group]

[EDIT] For the benefit of other readers, please review my comments to the OP. A better approach would be to create Views to be used by the other application. are describing can be achieved using Views without duplicating the data. To the calling application there should be no apparent distinction between querying a view and querying a table.
The solution provided above could be altered to create views rather than tables, the key is that the sql statements must be dynamic.

I got this working with a cursor.

Here's the code that I'm using-

DECLARE @Group varchar(200);
DECLARE @CreateTableScript nvarchar(MAX);

DECLARE groups CURSOR LOCAL FAST_FORWARD FOR
    SELECT
          Column_3 AS [Group]
        , N'IF OBJECT_ID(N''dbo.' + QUOTENAME('New_Table_' + Column_3) + ''', ''U'') IS NOT NULL 
                DROP TABLE dbo.' + QUOTENAME('New_Table_' + Column_3) + ';
            select Column_1, Column_2  
            into dbo.' + QUOTENAME('New_Table_' + Column_3) + '
            from Existing_Table where Column_3 = @Group;' AS CreateTableScript
    FROM (SELECT DISTINCT Column_3 FROM Existing_Table) AS groups;

OPEN groups;
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM groups INTO @Group, @CreateTableScript;
    IF @@FETCH_STATUS = -1 BREAK;
    PRINT @CreateTableScript;
    EXEC sp_executesql
          @CreateTableScript
        , N'@Group varchar(200)'
        , @Group = @Group;
END;
CLOSE groups;
DEALLOCATE groups;
GO

Thanks to @digimanus and @CHill60 for helping out on this that got me learn some good stuff around this.

这篇关于从现有表中为特定列的每个不同值创建一个新表，并使用此不同值命名新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！