使用动态SQL更改表结构并且不知道SQL Server 2008中的属性数量
如果我有一个像这样的表(我不知道列的名称和有多少列,在这个例子中有 5 个属性,有 20 行:
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
你如何得到这样的东西 (5 列 * 20 行 = 100 行
,其中每 20 行我们有上一个表的一列)
Id Ordinal Name Value
----------------------------
1 1 x1 438
2 1 x1 438
3 1 x1 438
4 1 x1 437
5 1 x1 438
6 1 x1 439
7 1 x1 440
8 1 x1 444
9 1 x1 451
10 1 x1 458
11 1 x1 465
12 1 x1 473
13 1 x1 481
14 1 x1 484
15 1 x1 484
16 1 x1 482
17 1 x1 481
18 1 x1 480
19 1 x1 481
20 1 x1 479
1 2 x2 498
2 2 x2 498
3 2 x2 498
4 2 x2 501
5 2 x2 498
6 2 x2 498
7 2 x2 5000
8 2 x2 5021
9 2 x2 5025
10 2 x2 5022
11 2 x2 525
12 2 x2 533
13 2 x2 544
14 2 x2 544
15 2 x2 532
16 2 x2 520
17 2 x2 522
18 2 x2 525
19 2 x2 515
20 2 x2 5000
1 3 x3 3625
2 3 x3 3625
3 3 x3 3625
4 3 x3 3625
5 3 x3 3626
6 3 x3 3626
7 3 x3 3627
8 3 x3 3631
9 3 x3 3635
10 3 x3 3640
11 3 x3 3646
12 3 x3 3652
13 3 x3 3658
14 3 x3 3661
15 3 x3 3669
16 3 x3 3685
17 3 x3 3682
18 3 x3 3694
19 3 x3 5018
20 3 x3 3696
1 4 x4 3645
2 4 x4 3648
3 4 x4 3629
4 4 x4 3626
5 4 x4 3629
6 4 x4 3629
7 4 x4 3628
8 4 x4 3634
9 4 x4 3639
10 4 x4 3644
11 4 x4 3670
12 4 x4 3676
13 4 x4 3678
14 4 x4 3665
15 4 x4 3662
16 4 x4 3664
17 4 x4 3661
18 4 x4 3664
19 4 x4 3664
20 4 x4 3661
1 5 x5 5000
2 5 x5 5000
3 5 x5 5000
4 5 x5 5000
5 5 x5 5000
6 5 x5 5000
7 5 x5 5000
8 5 x5 5000
9 5 x5 5000
10 5 x5 5000
11 5 x5 5000
12 5 x5 5000
13 5 x5 5000
14 5 x5 5000
15 5 x5 2945
16 5 x5 2952
17 5 x5 2955
18 5 x5 2948
19 5 x5 2956
20 5 x5 2953
这个的动态版本如何?在示例中,我有 5 个属性,但假设我不知道有多少个属性。
If I have a table like this (where I do not know the name of the columns and How many are there, in this example there are 5 attributes, with 20 rows:
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
How would you get something like this
(5 columns * 20 rows = 100 rows
where every 20 rows we have a column of previous table)
Id Ordinal Name Value
----------------------------
1 1 x1 438
2 1 x1 438
3 1 x1 438
4 1 x1 437
5 1 x1 438
6 1 x1 439
7 1 x1 440
8 1 x1 444
9 1 x1 451
10 1 x1 458
11 1 x1 465
12 1 x1 473
13 1 x1 481
14 1 x1 484
15 1 x1 484
16 1 x1 482
17 1 x1 481
18 1 x1 480
19 1 x1 481
20 1 x1 479
1 2 x2 498
2 2 x2 498
3 2 x2 498
4 2 x2 501
5 2 x2 498
6 2 x2 498
7 2 x2 5000
8 2 x2 5021
9 2 x2 5025
10 2 x2 5022
11 2 x2 525
12 2 x2 533
13 2 x2 544
14 2 x2 544
15 2 x2 532
16 2 x2 520
17 2 x2 522
18 2 x2 525
19 2 x2 515
20 2 x2 5000
1 3 x3 3625
2 3 x3 3625
3 3 x3 3625
4 3 x3 3625
5 3 x3 3626
6 3 x3 3626
7 3 x3 3627
8 3 x3 3631
9 3 x3 3635
10 3 x3 3640
11 3 x3 3646
12 3 x3 3652
13 3 x3 3658
14 3 x3 3661
15 3 x3 3669
16 3 x3 3685
17 3 x3 3682
18 3 x3 3694
19 3 x3 5018
20 3 x3 3696
1 4 x4 3645
2 4 x4 3648
3 4 x4 3629
4 4 x4 3626
5 4 x4 3629
6 4 x4 3629
7 4 x4 3628
8 4 x4 3634
9 4 x4 3639
10 4 x4 3644
11 4 x4 3670
12 4 x4 3676
13 4 x4 3678
14 4 x4 3665
15 4 x4 3662
16 4 x4 3664
17 4 x4 3661
18 4 x4 3664
19 4 x4 3664
20 4 x4 3661
1 5 x5 5000
2 5 x5 5000
3 5 x5 5000
4 5 x5 5000
5 5 x5 5000
6 5 x5 5000
7 5 x5 5000
8 5 x5 5000
9 5 x5 5000
10 5 x5 5000
11 5 x5 5000
12 5 x5 5000
13 5 x5 5000
14 5 x5 5000
15 5 x5 2945
16 5 x5 2952
17 5 x5 2955
18 5 x5 2948
19 5 x5 2956
20 5 x5 2953
How would be the dynamic version of this? In the example I have 5 attributes, but Let´s say I do not know how many atrributes are there.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
发布评论
评论(2)
甜是你2024-11-11 22:32:11
DECLARE @DynSQL NVARCHAR(MAX)
SELECT @DynSQL = ISNULL(@DynSQL+ ',','') + QUOTENAME(name)
FROM sys.columns WHERE object_id=OBJECT_ID('dbo.YourTable')
SET @DynSQL = '
;WITH T AS
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS Id
FROM YourTable)
SELECT Id,
CAST(SUBSTRING(Name,2,10) AS INT) AS Ordinal,
Name,
Value
FROM T
UNPIVOT(Value for Name in (' + @DynSQL + ')) U
ORDER BY Ordinal,Id'
EXEC(@DynSQL)
~没有更多了~
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
尽管我支持@Martin 的解决方案,但我还是发布了此内容。他的更优雅(我仍然直观地知道什么时候最好使用 PIVOT 和 UNPIVOT),但也更无情,因为原始表的列必须是完全相同的数据类型并遵循“x####” ” 命名约定。
由于我在构建查询时使用了更多的列替换,因此我使用
+ CHAR(13) + CHAR(10)
向动态 SQL,试图使代码(前后)更清晰。
(附录)
将动态创建的查询的结果插入到临时表中相当容易,只要您不必动态创建临时表即可。
临时表的持续时间与创建它们的“会话”一样长。如果在存储过程开始时创建,它们将持续存在,直到该存储过程(及其调用的任何过程)完成;如果在动态创建的脚本中创建,它们将持续存在,直到该脚本完成。因此,您可以构建一个“SELECT…INTO #…”动态脚本,该表将被填充,然后在该脚本完成执行时被删除。要保存数据的时间比这更长,您必须在调用动态脚本之前创建临时表,如下所示:
分配适当的数据类型和可空性,然后用以下内容填充它:
I'm posting this, even though I'm upvoting @Martin's solution. His is much more elegant (I've still to intuitively know when it's best to use PIVOT and UNPIVOT), but also more unforgiving, as the original tables' columns must be all the exact same data type and follow the "x####" naming convention.
Since I use more column substitution when building the query, I used
+ CHAR(13) + CHAR(10)
to add line breaks to thedynamic SQL, in an attempt to make the code (both before and after) more legible.
(Addenda)
Inserting the results of a dynamically created query into a temp table is fairly easy, so long as you don't have to dynamically create the temp table.
Temp tables only last for as long as the “session” in which they are created. If created at the start of a stored procedure, they persist until that stored procedure (and any procedures it calls) is finished; if created within a dynamically created script, they persist until that script finishes. So you could build a “SELECT… INTO #…” dynamic script, teh table would be populated, and would then be dropped when that script finished execution. To persist the data longer than that, you’d have to create the temp table before calling the dynamic script, something like so:
assigning the appropriate data types and nullability, and then populating it with