SQL Server管理工具-针对n列，n行的UNPIVOT查询

Question

我想将数据集从这样的形式转换为非标准化形式：

转换后的数据如下所示：

+-------------+-----------------+---------+
| Scenario ID | Distribution ID |  Value  |
+-------------+-----------------+---------+
|           0 | Number1         |      10 |
|           0 | Number2         |      19 |
|           0 | Number3         |    34.3 |
|           0 | Number4         |   60.31 |
|           0 | Number5         | 104.527 |
+-------------+-----------------+---------+

使用SQL系统管理工作室。

我认为我应该使用基于类似这样的代码：

SELECT *
FROM
(
  SELECT 1
  FROM table_name
) AS cp 
UNPIVOT 
(
  Scenario FOR Scenarios IN (*
) AS up;

有谁能帮助我吗？我不知道如何编码，刚开始学习。提前感谢！

- user10484829

---

我在下面得到的建议后应用，结构已经完美，但是数值似乎不正确，它只捕捉了前两位数字（这些数值在十亿级别加上两位小数点）。 - user10484829

非常感谢您对另一个问题的理解。正如您所看到的，这个问题包括了您尝试过的内容，并且更加具体，很高兴看到它是如何为您工作的！欢迎来到下一个特权级别，例如投票或标记事物;-) - GhostCat

Answer 1

如果您需要一个动态的 unpivot 解决方案（可以处理任意数量的列），请尝试以下方法：

create table [dbo].[Test] ([ScenarioID] int,        [Number1] decimal(10,3), 
                           [Number2] decimal(10,3), [Number3] decimal(10,3),
                           [Number4] decimal(10,3), [Number5] decimal(10,3)) 

insert into [dbo].[Test] select 0, 10, 19, 34.3, 60.31, 104.527

declare @sql  nvarchar(max) = '' 
declare @cols nvarchar(max) = '' 

select @cols = @cols +','+ QUOTENAME(COLUMN_NAME) 
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_SCHEMA='dbo' and TABLE_NAME='test' and COLUMN_NAME like 'Number%' 
order by ORDINAL_POSITION 

set @cols = substring(@cols, 2, LEN(@cols)) 

set @sql = @sql + ' select u.[ScenarioID], u.[DistributionID], u.[Value]  
                     from [dbo].[Test] s  
                     unpivot  
                     (  
                     [Value]   
                     for [DistributionID] in ('+ @cols + ')  
                     ) u;' 

execute(@sql)

Result:

- Andrea

Answer 2

您可以使用VALUES：

SELECT T.scenarioId, s.*
FROM tab t
CROSS APPLY (VALUES ('Number1', t.Number1),
                    ('Number2', t.Number2)) AS s(DistId, Val)

- Lukasz Szozda

Answer 3

我使用 cross apply 来实现这个功能：

select t.scenarioid, v.*
from t cross apply
     (values ('Number1', number1), ('Number2', number2), . . . 
     ) v(distributionId, number);

你需要列出所有数字。

为什么我更喜欢使用cross apply而不是unpivot？我发现unpivot语法非常具体，它几乎只能做一件事。另一方面，apply引入了横向连接，这些非常强大，可以在许多不同的情况下使用。

- Gordon Linoff

---

这个可行，非常感谢！！ :) 谢谢，我会进一步了解这些可能性！ - user10484829

Answer 4

我会使用 apply ：

select t.scenarioid, tt.distributionId, tt.value
from table t cross apply
     ( values (Number1, 'Number1'), (Number2, 'Number2'), . . .   
     ) tt (value, distributionId);

是的，您需要在第一次列出所有可能的数字。

- Yogesh Sharma

---

谢谢！看起来它已经生效了！现在我只需要将它保存到数据库中。我需要一直列出所有的（Number1，'Number1'）...吗？ - user10484829

嘿，查询可以工作了，我找到了如何将这些结果放入新表中，但是值不完整（我的值在十亿级别，解除枢轴后只得到整数），我不知道如何找出原因。我尝试声明变量类型：创建表test2（[ScenarioID] int，[Distribution] varchar（50），                            [Value] decimal（20,3）） 插入到test2中 从test1 t交叉应用选择t [Concatenated ID]，tt [Distribution]，tt [Value]的值 （值（1-6-0-00-00，'1-6-0-00-00'），...但值仍然不正确... - user10484829

我不知道如何在您的示例中搜索添加的t字母：t.scenarioid或tt.distributionId。我不知道如何调用它来搜索它？如果您建议任何关于此的网站/视频，我会非常感激！ - user10484829

@user10484829. . . t 是你的表别名，没有其他意思，它可能是不同的。 - Yogesh Sharma