SQL矩阵到数组 [英] SQL Matrix to array

问题描述

我正在使用T-SQL，并且有一个看起来像Matrix(8x8)的表. 我的目标是使用Pivot将Matrix(表)设置为数组...我已经阅读了论坛以及设法找到的更多内容，但仍然无法为其编写代码...

I am working with T-SQL and I have a table that looks like Matrix (8x8). My objective is to make that Matrix (table) into array using Pivot ... I have read forums and more stuff that i managed to find but i still can't make a code for it ...

 ID     Bucket     B1     B2     B3     B4 
 5        1        20     21     45     12
 6        2        12     18     19     48
 7        3        19     78     40     78  
 8        4        72     34     12     17

所以我要做的就是从该表中制作三维数组"，并将行，列和值保存为……

So all I need to do is to make "three dimensional array" from that table, and to save row, column and value ... to be something like this

Row    Column   Value 
 1       1       20
 1       2       21
 1       3       45
 1       2       12
 etc
 etc
 etc
 4       3       12
 4       4       17

有人知道如何在T-SQL中编写该代码吗?

Does anyone have any idea how I could write that code in T-SQL?

ps.我这样做的原因是因为我想将矩阵与其自身相乘.因此，如果我在数据透视表中将其乘以起来会更容易. 谢谢

ps. Reason i'm doing this, is because i want to multiply my matrix with itself. So it's easier to multiply it if i have it in pivot table. Thank you

推荐答案

尝试取消透视数据:

DECLARE @table TABLE (id INT, Bucket INT, B1 INT, B2 INT, B3 INT, B4 INT)
INSERT INTO @table VALUES
(5,1,20,21,45,12),
(6,2,12,18,19,48),
(7,3,19,78,40,78),
(8,4,72,34,12,17)

SELECT rn AS [ROW],
VALUE AS [ColumnNumber],
orders AS [VALUE] 
FROM
(
SELECT ROW_NUMBER () OVER (ORDER BY id) AS rn,id, Bucket, B1 [1], B2 [2], B3 [3], B4 [4]
FROM @table
) AS t
UNPIVOT
(
    orders
    FOR VALUE IN([1], [2],[3],[4])
) AS pvt

为此 MSDN 文档进行检查PIVOT和UNPIVOT的更多详细信息.

Check this MSDN Doc for more details of PIVOT and UNPIVOT.

这篇关于SQL矩阵到数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！