当前位置： 文江博客话题详情

在SQL Server 2008中获取3个表，避免使用两个循环和动态SQL

发布于 2024-11-04 20:17:50 字数 4764 浏览 0 评论 0原文

我想从 SQL 中名为 @X_Table 的一个表（该表具有 原始表 的列名称）中获取 3 个表 2008：

原始表有很多行和 5 个属性（我可以有更多属性，例如 30 个，但在示例中我有 5 个属性）：

x1  x2   x3   x4   x5    
----------------------------
438 498  3625 3645 5000
438 498  3625 3648 5000
438 498  3625 3629 5000
437 501  3625 3626 5000
438 498  3626 3629 5000
439 498  3626 3629 5000
440 5000 3627 3628 5000
444 5021 3631 3634 5000
451 5025 3635 3639 5000
458 5022 3640 3644 5000
465 525  3646 3670 5000
473 533  3652 3676 5000
481 544  3658 3678 5000
484 544  3661 3665 5000
484 532  3669 3662 2945
482 520  3685 3664 2952
481 522  3682 3661 2955
480 525  3694 3664 2948
481 515  5018 3664 2956
479 5000 3696 3661 2953 
**...(EVEN MORE ROWS LIKE 100,000)** ...

首先，我在其他表中获取列的名称（var @table_name 指的是上表）：

INSERT  @X_Table (ID,NAME) 
SELECT  [ID] = ORDINAL_POSITION, [NAME] = COLUMN_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = @table_name

所以我得到 @X_Table:

@X_Table:

ID  NAME
------------
1   x1
2   x2
3   x3
4   x4
5   x5

然后对于每一列 我想要该列中所有值的总和 所以 xi 中的所有值，所以 sum(x1), sum(x2), sum(x3), sum(x4), sum(x5)`

表 1：INT、VARCHAR、FLOAT

ID Name  value
---------------------------
1   x1  48431
2   x2  138420
3   x3  192071
4   x4  192041
5   x5  204602

然后针对每一列 例如，我想要该列中所有值的总和乘以每列
对于 x1 列：sum(x1*x1)、sum(x1*x2)、sum(x1*x3)、sum(x1*x4)、sum(x1*x5)
对于 x2 列：sum(x2*x2)、sum(x2*x3)、sum(x2*x4)、sum(x2*x5)
对于 x3 列：sum(x3*x3)、sum(x3*x4)、sum(x3*x5)
对于 x4 列：sum(x4*x4), sum(x4*x5)
对于列 x5： sum(x5*x5)

表 2：INT、VARCHAR、FLOAT

ID   Name     value
---------------------------
1   SUM(x1*x1)  83926421
2   SUM(x1*x2)  162997894
3   SUM(x1*x3)  186865166
4   SUM(x1*x4)  156224385
5   SUM(x1*x5)  168573557
6   SUM(x2*x2)  598228836
7   SUM(x2*x3)  542331010
8   SUM(x2*x4)  460990820
9   SUM(x2*x5)  512335365
10  SUM(x3*x3)  797430261
11  SUM(x3*x4)  659040569
12  SUM(x3*x5)  723779398
13  SUM(x4*x4)  677091478
14  SUM(x4*x5)  722922237
15  SUM(x5*x5)  808976398

然后，对于最后一个表，我想要对角线，或者在其他表中是表的 xii上面

表 3：INT、VARCHAR、FLOAT

ID   Name     value
---------------------------
1   SUM(x1*x1)  83926421
2   SUM(x2*x2)  598228836
3   SUM(x3*x3)  797430261
4   SUM(x4*x4)  677091478
5   SUM(x5*x5)  808976398

所以为了做到这一点，我正在采用这种方法，但我知道这可以优化，

SET @d = 5
WHILE (@counterI <= @d) BEGIN
        SELECT @nameThird   = NAME  FROM @X_Table where ID =  @counterI;
        SET    @nameFirst   = @nameThird;

    --INSERT FIRST TABLE
    SET @queryFirst = 'INSERT #FIRST_T (ID,NAME,Value) SELECT '+ CAST(@counterI AS VARCHAR)+' , '''+@nameFirst+''', SUM('+@nameFirst+') FROM '+ @table_name;
    EXEC (@queryFirst);

    --GET VALUE TO INSERT IN THIRD TABLE       
    SET @queryThird = 'INSERT #THIRD_T (ID,NAME,Value) SELECT '+CAST(@counterI AS VARCHAR)+' , '''+@nameThird+'*'+@nameThird+''', SUM('+@nameThird +'*'+@nameThird+') FROM '+  @table_name;
    EXEC (@queryThird);

    --Xij  
    WHILE (@counterJ <= @d) BEGIN           
        SELECT @nameThird2  = NAME  FROM @X_Table where ID =  @counterJ;
        SET @queryThird = 'INSERT #SECOND_T (ID,NAME,Value) SELECT '+CAST(@n AS VARCHAR)+' , '''+@nameThird+'*'+@nameThird2+''', SUM('+@nameThird +'*'+@nameThird2+') FROM '+  @table_name;
        EXEC (@queryThird);         
        SET @counterJ  = @counterJ + 1; 
        SET @n = @n +1  
    END         
    SET @counterI   = @counterI + 1; --reduce space
    SET @counterJ   = @counterI;
END
  • 如果没有这 2 个，你会如何做到这一点 循环？,

（对于超过30个属性的表来说需要很多时间...）

---------------------------------------- ---编辑----------------------------------------

当超过 10 列时，我得到 @table1 ( @Thomas 回答）

ID 名称值

1   x1  8029145
2   x10 15453498
3   x11 13909514
4   x12 11336348
5   x13 11598240
6   x14 11951291
7   x15 12034693
8   x16 6558719
9   x17 5400520
10  x18 4966450
11  x19 5773049
12  x2  12696346
13  x20 5872404
14  x21 5542875
15  x22 9700954
16  x23 8484327
17  x24 8612340
18  x25 129470
19  x3  135818770

是否有某种方法可以按名称

x1,x2,x3,x4,x5...,x9,x10,x11....

而不是对

x1,x10,x11,x19,x2,x3....?

它们进行排序所以我们得到

ID Name Value
 -----------------
    1   x1  8029145
    2   x2  12696346
    3   x3  135818770
    ...
原文

I want to get 3 tables from one table named @X_Table (this table has the name of columns of original table) in SQL 2008:

The Original table has lots of rows and 5 atributes (I could have more atributes like 30, but in the example I have 5 atributes):

x1  x2   x3   x4   x5    
----------------------------
438 498  3625 3645 5000
438 498  3625 3648 5000
438 498  3625 3629 5000
437 501  3625 3626 5000
438 498  3626 3629 5000
439 498  3626 3629 5000
440 5000 3627 3628 5000
444 5021 3631 3634 5000
451 5025 3635 3639 5000
458 5022 3640 3644 5000
465 525  3646 3670 5000
473 533  3652 3676 5000
481 544  3658 3678 5000
484 544  3661 3665 5000
484 532  3669 3662 2945
482 520  3685 3664 2952
481 522  3682 3661 2955
480 525  3694 3664 2948
481 515  5018 3664 2956
479 5000 3696 3661 2953 
**...(EVEN MORE ROWS LIKE 100,000)** ...

First I am getting in other table the names of the columns(the var @table_name refers to the table above):

INSERT  @X_Table (ID,NAME) 
SELECT  [ID] = ORDINAL_POSITION, [NAME] = COLUMN_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = @table_name

So I get the @X_Table:

@X_Table:

ID  NAME
------------
1   x1
2   x2
3   x3
4   x4
5   x5

Then for each column I want the sum of all values in that column
so all the values in xi, so
sum(x1), sum(x2), sum(x3), sum(x4), sum(x5)`

Table 1: INT,VARCHAR,FLOAT

ID Name  value
---------------------------
1   x1  48431
2   x2  138420
3   x3  192071
4   x4  192041
5   x5  204602

Then for each column I want the sum of all values in that column multiplied by each column for example
for column x1: sum(x1*x1), sum(x1*x2), sum(x1*x3), sum(x1*x4), sum(x1*x5)
for column x2: sum(x2*x2), sum(x2*x3), sum(x2*x4), sum(x2*x5)
for column x3: sum(x3*x3), sum(x3*x4), sum(x3*x5)
for column x4: sum(x4*x4), sum(x4*x5)
for column x5: sum(x5*x5)

Table 2: INT, VARCHAR, FLOAT

ID   Name     value
---------------------------
1   SUM(x1*x1)  83926421
2   SUM(x1*x2)  162997894
3   SUM(x1*x3)  186865166
4   SUM(x1*x4)  156224385
5   SUM(x1*x5)  168573557
6   SUM(x2*x2)  598228836
7   SUM(x2*x3)  542331010
8   SUM(x2*x4)  460990820
9   SUM(x2*x5)  512335365
10  SUM(x3*x3)  797430261
11  SUM(x3*x4)  659040569
12  SUM(x3*x5)  723779398
13  SUM(x4*x4)  677091478
14  SUM(x4*x5)  722922237
15  SUM(x5*x5)  808976398

Then for the last table I want the diagonal, or in other the xii of the table above

Table 3: INT, VARCHAR, FLOAT

ID   Name     value
---------------------------
1   SUM(x1*x1)  83926421
2   SUM(x2*x2)  598228836
3   SUM(x3*x3)  797430261
4   SUM(x4*x4)  677091478
5   SUM(x5*x5)  808976398

So To do so I am doing this approach, but I know This can be optimized,

SET @d = 5
WHILE (@counterI <= @d) BEGIN
        SELECT @nameThird   = NAME  FROM @X_Table where ID =  @counterI;
        SET    @nameFirst   = @nameThird;

    --INSERT FIRST TABLE
    SET @queryFirst = 'INSERT #FIRST_T (ID,NAME,Value) SELECT '+ CAST(@counterI AS VARCHAR)+' , '''+@nameFirst+''', SUM('+@nameFirst+') FROM '+ @table_name;
    EXEC (@queryFirst);

    --GET VALUE TO INSERT IN THIRD TABLE       
    SET @queryThird = 'INSERT #THIRD_T (ID,NAME,Value) SELECT '+CAST(@counterI AS VARCHAR)+' , '''+@nameThird+'*'+@nameThird+''', SUM('+@nameThird +'*'+@nameThird+') FROM '+  @table_name;
    EXEC (@queryThird);

    --Xij  
    WHILE (@counterJ <= @d) BEGIN           
        SELECT @nameThird2  = NAME  FROM @X_Table where ID =  @counterJ;
        SET @queryThird = 'INSERT #SECOND_T (ID,NAME,Value) SELECT '+CAST(@n AS VARCHAR)+' , '''+@nameThird+'*'+@nameThird2+''', SUM('+@nameThird +'*'+@nameThird2+') FROM '+  @table_name;
        EXEC (@queryThird);         
        SET @counterJ  = @counterJ + 1; 
        SET @n = @n +1  
    END         
    SET @counterI   = @counterI + 1; --reduce space
    SET @counterJ   = @counterI;
END
  • How would you do this without these 2
    loops?,

(It takes a lot of time for tables with more than 30 attributes...)

-----------------------------EDIT----------------------------

When having more than 10 columns I am getting for the @table1 (answer by @Thomas)

ID Name Value

1   x1  8029145
2   x10 15453498
3   x11 13909514
4   x12 11336348
5   x13 11598240
6   x14 11951291
7   x15 12034693
8   x16 6558719
9   x17 5400520
10  x18 4966450
11  x19 5773049
12  x2  12696346
13  x20 5872404
14  x21 5542875
15  x22 9700954
16  x23 8484327
17  x24 8612340
18  x25 129470
19  x3  135818770

Is there some way to sort them by Name

x1,x2,x3,x4,x5...,x9,x10,x11....

instead of

x1,x10,x11,x19,x2,x3....?

So we get

ID Name Value
 -----------------
    1   x1  8029145
    2   x2  12696346
    3   x3  135818770
    ...

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

扫码二维码加入Web技术交流群

发布评论

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

评论（2）

你对谁都笑 2024-11-11 20:17:50

（扩展源数据以说明序数的使用）

如果对结构进行规范化，这个问题会容易得多。由于您尝试对列进行算术运算，因此它们需要是行而不是列。

Declare @X_Table Table
    (
    Id int not null Identity(1,1) Primary Key
    , x1 float not null
    , x2 float not null
    , x3 float not null
    , x4 float not null
    , x5 float not null
    , x111 float not null
    , x112 float not null
    , x113 float not null
    , x114 float not null
    , x115 float not null
    , x11 float not null
    , x12 float not null
    , x13 float not null
    , x14 float not null
    , x15 float not null
    )

Insert @X_Table( x1, x2, x3, x4, x5
    , x111, x112, x113, x114, x115 
    , x11, x12, x13, x14, x15 
    )
Select 438, 498, 3625, 3645, 5000, 438, 498, 3625, 3645, 5000, 438, 498, 3625, 3645, 5000
Union All Select 438, 498, 3625, 3648, 5000, 438, 498, 3625, 3648, 5000, 438, 498, 3625, 3648, 5000
Union All Select 438, 498, 3625, 3629, 5000, 438, 498, 3625, 3629, 5000, 438, 498, 3625, 3629, 5000
Union All Select 437, 501, 3625, 3626, 5000, 437, 501, 3625, 3626, 5000, 437, 501, 3625, 3626, 5000
Union All Select 438, 498, 3626, 3629, 5000, 438, 498, 3626, 3629, 5000, 438, 498, 3626, 3629, 5000
Union All Select 439, 498, 3626, 3629, 5000, 439, 498, 3626, 3629, 5000, 439, 498, 3626, 3629, 5000
Union All Select 440, 5000, 3627, 3628, 5000, 440, 5000, 3627, 3628, 5000, 440, 5000, 3627, 3628, 5000
Union All Select 444, 5021, 3631, 3634, 5000, 444, 5021, 3631, 3634, 5000, 444, 5021, 3631, 3634, 5000
Union All Select 451, 5025, 3635, 3639, 5000, 451, 5025, 3635, 3639, 5000, 451, 5025, 3635, 3639, 5000
Union All Select 458, 5022, 3640, 3644, 5000, 458, 5022, 3640, 3644, 5000, 458, 5022, 3640, 3644, 5000
Union All Select 465, 525, 3646, 3670, 5000, 465, 525, 3646, 3670, 5000, 465, 525, 3646, 3670, 5000
Union All Select 473, 533, 3652, 3676, 5000, 473, 533, 3652, 3676, 5000, 473, 533, 3652, 3676, 5000
Union All Select 481, 544, 3658, 3678, 5000, 481, 544, 3658, 3678, 5000, 481, 544, 3658, 3678, 5000
Union All Select 484, 544, 3661, 3665, 5000, 484, 544, 3661, 3665, 5000, 484, 544, 3661, 3665, 5000
Union All Select 484, 532, 3669, 3662, 2945, 484, 532, 3669, 3662, 2945, 484, 532, 3669, 3662, 2945
Union All Select 482, 520, 3685, 3664, 2952, 482, 520, 3685, 3664, 2952, 482, 520, 3685, 3664, 2952
Union All Select 481, 522, 3682, 3661, 2955, 481, 522, 3682, 3661, 2955, 481, 522, 3682, 3661, 2955
Union All Select 480, 525, 3694, 3664, 2948, 480, 525, 3694, 3664, 2948, 480, 525, 3694, 3664, 2948
Union All Select 481, 515, 5018, 3664, 2956, 481, 515, 5018, 3664, 2956, 481, 515, 5018, 3664, 2956
Union All Select 479, 5000, 3696, 3661, 2953, 479, 5000, 3696, 3661, 2953, 479, 5000, 3696, 3661, 2953

下表显然是静态的。然而，如果您要涉及动态 SQL（在 T-SQL 中不建议这样做，但显然是可行的），则下一个表的填充将是唯一的部分。

Declare @X_Table_Normalized Table
    (
    Id int not null
    , Ordinal int not null
    , Name varchar(10) not null
    , Value float not null
    )

Insert @X_Table_Normalized( Id, Ordinal, Name, Value )
Select Id, 1, 'x1', x1 From @X_Table
Union All Select Id, 2, 'x2', x2 From @X_Table
Union All Select Id, 3, 'x3', x3 From @X_Table
Union All Select Id, 4, 'x4', x4 From @X_Table
Union All Select Id, 5, 'x5', x5 From @X_Table
Union All Select Id, 6, 'x111', x111 From @X_Table
Union All Select Id, 7, 'x112', x112 From @X_Table
Union All Select Id, 8, 'x113', x113 From @X_Table
Union All Select Id, 9, 'x114', x114 From @X_Table
Union All Select Id, 10, 'x115', x115 From @X_Table
Union All Select Id, 11, 'x11', x11 From @X_Table
Union All Select Id, 12, 'x12', x12 From @X_Table
Union All Select Id, 13, 'x13', x13 From @X_Table
Union All Select Id, 14, 'x14', x14 From @X_Table
Union All Select Id, 15, 'x15', x15 From @X_Table

表 1 - 按（原始）列求和

Declare @Table1 Table
    (
    Id int not null
    , Name varchar(25) not null
    , Ordinal int not null
    , Value float not null
    )

Insert @Table1( Id, Name, Ordinal, Value )
Select Row_Number() Over( Order By Ordinal, Name )
    , Name, Ordinal, Sum(Value)
From @X_Table_Normalized
Group By Ordinal, Name

表 2 - 按（原始）列求积和

Declare @Table2 Table
    (
    Id int not null
    , Name varchar(25) not null
    , Ordinal int not null
    , Value float not null
    )

Insert @Table2( Id, Name, Ordinal, Value )
Select Row_Number() Over ( Order By T1.Ordinal, T1.Name, T2.Ordinal, T2.Name ) As Id
    , 'Sum(' + T1.Name + '*' + T2.Name + ')' As Name
    , T1.Ordinal + 100 * T2.Ordinal
    , Sum( T1.Value * T2.Value ) As Value
From @X_Table_Normalized As T1
    Join @X_Table_Normalized As T2
        On T2.Id = T1.Id
Where T1.Ordinal <= T2.Ordinal
Group By T1.Name, T1.Ordinal, T2.Name, T2.Ordinal

表 3 - 按（原始）列对角线求和的乘积 使用

Declare @Table3 Table
    (
    Id int not null
    , Name varchar(25) not null
    , Ordinal int not null
    , Value float not null
    )

Insert @Table3( Id, Name, Ordinal, Value )
Select Row_Number() Over ( Order By T1.Ordinal, T1.Name, T2.Ordinal, T2.Name ) As Id
    , 'Sum(' + T1.Name + '*' + T2.Name + ')' As Name
    , T1.Ordinal + 100 * T2.Ordinal
    , Sum( T1.Value * T2.Value ) As Value
From @X_Table_Normalized As T1
    Join @X_Table_Normalized As T2
        On T2.Id = T1.Id
Where T1.Ordinal = T2.Ordinal
Group By T1.Name, T1.Ordinal, T2.Name, T2.Ordinal

Select * From @Table1 Order By Ordinal
Select * From @Table2 Order By Ordinal
Select * From @Table3 Order By Ordinal

动态 SQL 填充规范化表

根据其使用方式，您可以封装使用动态 SQL 填充规范化表。如果这样做，您将需要使用实际表或临时表而不是临时变量。该代码可能类似于：

Declare @Sql nvarchar(max)

Set @Sql = 'Insert #X_Table_Normalized_Dynamic( Id, Ordinal, Name, Value )|'
Select @Sql = @Sql + ' Union All Select Id'
    + ', ' + Cast( Row_Number() Over ( Order By ORDINAL_POSITION ) As varchar(10) )
    + ', ' + QuoteName(COLUMN_NAME, '''')
    + ', ' + QuoteName(COLUMN_NAME)
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME = 'MySourceTable'

Set @Sql = Replace( @Sql, '| Union All ', ' ')

Exec( @Sql )

(Expanded source data to illustrate use of ordinal)

This problem is much easier if you normalize the structure. Since you are trying to do arithmetic on the columns, they need to be rows instead of columns.

Declare @X_Table Table
    (
    Id int not null Identity(1,1) Primary Key
    , x1 float not null
    , x2 float not null
    , x3 float not null
    , x4 float not null
    , x5 float not null
    , x111 float not null
    , x112 float not null
    , x113 float not null
    , x114 float not null
    , x115 float not null
    , x11 float not null
    , x12 float not null
    , x13 float not null
    , x14 float not null
    , x15 float not null
    )

Insert @X_Table( x1, x2, x3, x4, x5
    , x111, x112, x113, x114, x115 
    , x11, x12, x13, x14, x15 
    )
Select 438, 498, 3625, 3645, 5000, 438, 498, 3625, 3645, 5000, 438, 498, 3625, 3645, 5000
Union All Select 438, 498, 3625, 3648, 5000, 438, 498, 3625, 3648, 5000, 438, 498, 3625, 3648, 5000
Union All Select 438, 498, 3625, 3629, 5000, 438, 498, 3625, 3629, 5000, 438, 498, 3625, 3629, 5000
Union All Select 437, 501, 3625, 3626, 5000, 437, 501, 3625, 3626, 5000, 437, 501, 3625, 3626, 5000
Union All Select 438, 498, 3626, 3629, 5000, 438, 498, 3626, 3629, 5000, 438, 498, 3626, 3629, 5000
Union All Select 439, 498, 3626, 3629, 5000, 439, 498, 3626, 3629, 5000, 439, 498, 3626, 3629, 5000
Union All Select 440, 5000, 3627, 3628, 5000, 440, 5000, 3627, 3628, 5000, 440, 5000, 3627, 3628, 5000
Union All Select 444, 5021, 3631, 3634, 5000, 444, 5021, 3631, 3634, 5000, 444, 5021, 3631, 3634, 5000
Union All Select 451, 5025, 3635, 3639, 5000, 451, 5025, 3635, 3639, 5000, 451, 5025, 3635, 3639, 5000
Union All Select 458, 5022, 3640, 3644, 5000, 458, 5022, 3640, 3644, 5000, 458, 5022, 3640, 3644, 5000
Union All Select 465, 525, 3646, 3670, 5000, 465, 525, 3646, 3670, 5000, 465, 525, 3646, 3670, 5000
Union All Select 473, 533, 3652, 3676, 5000, 473, 533, 3652, 3676, 5000, 473, 533, 3652, 3676, 5000
Union All Select 481, 544, 3658, 3678, 5000, 481, 544, 3658, 3678, 5000, 481, 544, 3658, 3678, 5000
Union All Select 484, 544, 3661, 3665, 5000, 484, 544, 3661, 3665, 5000, 484, 544, 3661, 3665, 5000
Union All Select 484, 532, 3669, 3662, 2945, 484, 532, 3669, 3662, 2945, 484, 532, 3669, 3662, 2945
Union All Select 482, 520, 3685, 3664, 2952, 482, 520, 3685, 3664, 2952, 482, 520, 3685, 3664, 2952
Union All Select 481, 522, 3682, 3661, 2955, 481, 522, 3682, 3661, 2955, 481, 522, 3682, 3661, 2955
Union All Select 480, 525, 3694, 3664, 2948, 480, 525, 3694, 3664, 2948, 480, 525, 3694, 3664, 2948
Union All Select 481, 515, 5018, 3664, 2956, 481, 515, 5018, 3664, 2956, 481, 515, 5018, 3664, 2956
Union All Select 479, 5000, 3696, 3661, 2953, 479, 5000, 3696, 3661, 2953, 479, 5000, 3696, 3661, 2953

The following table is clearly static. However, if you were going to involve dynamic SQL (which is not recommended in T-SQL but obviously doable), the population of this next table would be the only part.

Declare @X_Table_Normalized Table
    (
    Id int not null
    , Ordinal int not null
    , Name varchar(10) not null
    , Value float not null
    )

Insert @X_Table_Normalized( Id, Ordinal, Name, Value )
Select Id, 1, 'x1', x1 From @X_Table
Union All Select Id, 2, 'x2', x2 From @X_Table
Union All Select Id, 3, 'x3', x3 From @X_Table
Union All Select Id, 4, 'x4', x4 From @X_Table
Union All Select Id, 5, 'x5', x5 From @X_Table
Union All Select Id, 6, 'x111', x111 From @X_Table
Union All Select Id, 7, 'x112', x112 From @X_Table
Union All Select Id, 8, 'x113', x113 From @X_Table
Union All Select Id, 9, 'x114', x114 From @X_Table
Union All Select Id, 10, 'x115', x115 From @X_Table
Union All Select Id, 11, 'x11', x11 From @X_Table
Union All Select Id, 12, 'x12', x12 From @X_Table
Union All Select Id, 13, 'x13', x13 From @X_Table
Union All Select Id, 14, 'x14', x14 From @X_Table
Union All Select Id, 15, 'x15', x15 From @X_Table

Table 1 - Sum by (original) column

Declare @Table1 Table
    (
    Id int not null
    , Name varchar(25) not null
    , Ordinal int not null
    , Value float not null
    )

Insert @Table1( Id, Name, Ordinal, Value )
Select Row_Number() Over( Order By Ordinal, Name )
    , Name, Ordinal, Sum(Value)
From @X_Table_Normalized
Group By Ordinal, Name

Table 2 - Product-Sums by (original) column

Declare @Table2 Table
    (
    Id int not null
    , Name varchar(25) not null
    , Ordinal int not null
    , Value float not null
    )

Insert @Table2( Id, Name, Ordinal, Value )
Select Row_Number() Over ( Order By T1.Ordinal, T1.Name, T2.Ordinal, T2.Name ) As Id
    , 'Sum(' + T1.Name + '*' + T2.Name + ')' As Name
    , T1.Ordinal + 100 * T2.Ordinal
    , Sum( T1.Value * T2.Value ) As Value
From @X_Table_Normalized As T1
    Join @X_Table_Normalized As T2
        On T2.Id = T1.Id
Where T1.Ordinal <= T2.Ordinal
Group By T1.Name, T1.Ordinal, T2.Name, T2.Ordinal

Table 3 - Product-Sums diagonally by (original) column

Declare @Table3 Table
    (
    Id int not null
    , Name varchar(25) not null
    , Ordinal int not null
    , Value float not null
    )

Insert @Table3( Id, Name, Ordinal, Value )
Select Row_Number() Over ( Order By T1.Ordinal, T1.Name, T2.Ordinal, T2.Name ) As Id
    , 'Sum(' + T1.Name + '*' + T2.Name + ')' As Name
    , T1.Ordinal + 100 * T2.Ordinal
    , Sum( T1.Value * T2.Value ) As Value
From @X_Table_Normalized As T1
    Join @X_Table_Normalized As T2
        On T2.Id = T1.Id
Where T1.Ordinal = T2.Ordinal
Group By T1.Name, T1.Ordinal, T2.Name, T2.Ordinal

Select * From @Table1 Order By Ordinal
Select * From @Table2 Order By Ordinal
Select * From @Table3 Order By Ordinal

Populating the normalized table with dynamic SQL

Depending on how this is used, you might encapsulate the population of the normalized table using dynamic SQL. If you do, you will need to use real tables or temp tables as opposed to temp variables. That code might look something like:

Declare @Sql nvarchar(max)

Set @Sql = 'Insert #X_Table_Normalized_Dynamic( Id, Ordinal, Name, Value )|'
Select @Sql = @Sql + ' Union All Select Id'
    + ', ' + Cast( Row_Number() Over ( Order By ORDINAL_POSITION ) As varchar(10) )
    + ', ' + QuoteName(COLUMN_NAME, '''')
    + ', ' + QuoteName(COLUMN_NAME)
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME = 'MySourceTable'

Set @Sql = Replace( @Sql, '| Union All ', ' ')

Exec( @Sql )
回复 原文
知你几分 2024-11-11 20:17:50

我在这里采用另一种方法，但为什么不创建一个视图并从中派生其他表。

优点

  • 速度更快，因为一切仍然基于设置。
  • 更具可读性

动态创建视图

DECLARE @Statement VARCHAR(MAX) = 'CREATE VIEW VIEW_OriginalTable (ID, Name, Value) AS (' + CHAR(13) + CHAR(10)
SELECT  @Statement =  @Statement 
                      + 'UNION ALL SELECT ' 
                      + CAST(ORDINAL_POSITION AS VARCHAR(32)) 
                      + ', ''' 
                      + COLUMN_NAME
                      + ''', SUM('
                      + COLUMN_NAME
                      + ') FROM OriginalTable'
                      + CHAR(13) + CHAR(10)
FROM    INFORMATION_SCHEMA.COLUMNS 
WHERE   TABLE_NAME = 'OriginalTable'
SELECT @Statement = REPLACE(@Statement, 'UNION ALL SELECT 1,', 'SELECT 1,') + ')'
EXEC (@Statement)
GO

三个结果表

/*
  Table1
*/    
SELECT  *
FROM    VIEW_OriginalTable

/*
  Table2
*/    
SELECT  [ID] = ROW_NUMBER() OVER (ORDER BY t1.ID)
        , [Name] = t1.Name + '*' + t2.Name
        , [Value] = t1.Value * t2.Value
FROM    VIEW_OriginalTable t1
        INNER JOIN VIEW_OriginalTable t2 ON t2.ID >= t1.ID

/*
  Table3
*/    
SELECT  [ID] = t1.ID
        , [Name] = t1.Name + '*' + t1.Name
        , [Value] = t1.Value * t1.Value
FROM    VIEW_OriginalTable t1

I'm taking another aproach here but why don't you just create a view and derive your other tables from that.

Advantage

  • Much faster as everything is still set based.
  • More readable

Dynamic Created View

DECLARE @Statement VARCHAR(MAX) = 'CREATE VIEW VIEW_OriginalTable (ID, Name, Value) AS (' + CHAR(13) + CHAR(10)
SELECT  @Statement =  @Statement 
                      + 'UNION ALL SELECT ' 
                      + CAST(ORDINAL_POSITION AS VARCHAR(32)) 
                      + ', ''' 
                      + COLUMN_NAME
                      + ''', SUM('
                      + COLUMN_NAME
                      + ') FROM OriginalTable'
                      + CHAR(13) + CHAR(10)
FROM    INFORMATION_SCHEMA.COLUMNS 
WHERE   TABLE_NAME = 'OriginalTable'
SELECT @Statement = REPLACE(@Statement, 'UNION ALL SELECT 1,', 'SELECT 1,') + ')'
EXEC (@Statement)
GO

Three result tables

/*
  Table1
*/    
SELECT  *
FROM    VIEW_OriginalTable

/*
  Table2
*/    
SELECT  [ID] = ROW_NUMBER() OVER (ORDER BY t1.ID)
        , [Name] = t1.Name + '*' + t2.Name
        , [Value] = t1.Value * t2.Value
FROM    VIEW_OriginalTable t1
        INNER JOIN VIEW_OriginalTable t2 ON t2.ID >= t1.ID

/*
  Table3
*/    
SELECT  [ID] = t1.ID
        , [Name] = t1.Name + '*' + t1.Name
        , [Value] = t1.Value * t1.Value
FROM    VIEW_OriginalTable t1
回复 原文
~没有更多了~

关于作者

热鲨

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

留蓝

文章 0 评论 0

18790681156

文章 0 评论 0

zach7772

文章 0 评论 0

Wini

文章 0 评论 0

ayeshaaroy

文章 0 评论 0

初雪

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文