使用动态SQL更改表结构并且不知道SQL Server 2008中的属性数量

发布于 11-04 22:32 字数 2488 浏览 2 评论 0原文

如果我有一个像这样的表(我不知道列的名称和有多少列,在这个例子中有 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 技术交流群。

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

发布评论

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

评论(2

我不是你的备胎2024-11-11 22:32:11

尽管我支持@Martin 的解决方案,但我还是发布了此内容。他的更优雅(我仍然直观地知道什么时候最好使用 PIVOT 和 UNPIVOT),但也更无情,因为原始表的列必须是完全相同的数据类型并遵循“x####” ” 命名约定。

由于我在构建查询时使用了更多的列替换,因此我使用 + CHAR(13) + CHAR(10)
动态 SQL,试图使代码(前后)更清晰。

DECLARE
  @TableName  sysname
 ,@Command    nvarchar(max)

SET @TableName = 'YourTable'

--  Build a big dynamic set of UNION SELECT statements
SELECT @Command = isnull(@Command + char(13) + char(10) + 'union all ', '')
  + 'select ' + cast(column_id as varchar(10)) + ' Ordinal, ''' + name + ''' name, ' + name + ' value ' + char(13) + char(10)
  + ' from ' + @TableName 
 from sys.columns
 where object_id = object_id(@TableName)

--  Wrap the above as a subquery, to enable the use of row_number()
SET @Command = 'select row_number() over (partition by Ordinal order by Ordinal) Id, Ordinal, Name, Value' + char(13) + char(10)
 + ' from (' + @Command + ') bigUnion'

--PRINT @Command
EXECUTE (@Command)

(附录)

将动态创建的查询的结果插入到临时表中相当容易,只要您不必动态创建临时表即可。

临时表的持续时间与创建它们的“会话”一样长。如果在存储过程开始时创建,它们将持续存在,直到该存储过程(及其调用的任何过程)完成;如果在动态创建的脚本中创建,它们将持续存在,直到该脚本完成。因此,您可以构建一个“SELECT…INTO #…”动态脚本,该表将被填充,然后在该脚本完成执行时被删除。要保存数据的时间比这更长,您必须在调用动态脚本之前创建临时表,如下所示:

CREATE TABLE #MyTempTable
 (
   Id
  ,Ordinal
  ,Name
  ,Value
 )

分配适当的数据类型和可空性,然后用以下内容填充它:

INSERT #MyTempTable (Id, Ordinal, Name, Value)
 EXECUTE (@Command)

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 the
dynamic SQL, in an attempt to make the code (both before and after) more legible.

DECLARE
  @TableName  sysname
 ,@Command    nvarchar(max)

SET @TableName = 'YourTable'

--  Build a big dynamic set of UNION SELECT statements
SELECT @Command = isnull(@Command + char(13) + char(10) + 'union all ', '')
  + 'select ' + cast(column_id as varchar(10)) + ' Ordinal, ''' + name + ''' name, ' + name + ' value ' + char(13) + char(10)
  + ' from ' + @TableName 
 from sys.columns
 where object_id = object_id(@TableName)

--  Wrap the above as a subquery, to enable the use of row_number()
SET @Command = 'select row_number() over (partition by Ordinal order by Ordinal) Id, Ordinal, Name, Value' + char(13) + char(10)
 + ' from (' + @Command + ') bigUnion'

--PRINT @Command
EXECUTE (@Command)

(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:

CREATE TABLE #MyTempTable
 (
   Id
  ,Ordinal
  ,Name
  ,Value
 )

assigning the appropriate data types and nullability, and then populating it with

INSERT #MyTempTable (Id, Ordinal, Name, Value)
 EXECUTE (@Command)
甜是你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)
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)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文