如何在SQL Server中转置一个上三角矩阵

Question

如何在SQL Server中转置一个上三角矩阵

t-sqlsql-server-2008matrix

3

如果我有一个表格（上三角矩阵），数据类型为浮点数，例如：

columns_1  columns_2   columns_3   columns_4
     1            12          13          14  
  null             1          23          24   
  null          null           1          34   
  null          null        null           1

我需要遵循什么方法才能获得以下内容？

    columns_1 columns_2 columns_3 columns_4
            1        12        13        14
           12         1        23        24
           13        23         1        34
           14        24        34         1

在SQL server 2008中是否可能实现这一点？

- edgarmtze

有一个给定的固定列和行数？这些包含混合单词和数字的列是什么数据类型？ - Martin Smith

好的，数据类型是浮点数，矩阵（表）的维度已知。 - edgarmtze

顺便提一下，这里有一个与你的问题相关但不同的问题（链接：http://stackoverflow.com/questions/3505978/triangular-multiplication-matrix-generation/3509076#3509076）。它可能会给你一些进一步的想法。 - Martin Smith

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Martin Smith · Accepted Answer

这是一种实现方法。

CREATE PROC dbo.Transpose @TableSchema sysname,
                          @TableName   sysname,
                          @Debug       bit = 0
AS

  DECLARE @N INT

  DECLARE @cols TABLE(
    idx INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    col int NOT NULL )

  INSERT INTO @cols
  SELECT CAST(CASE WHEN COLUMN_NAME NOT LIKE '%[^0-9]%' THEN COLUMN_NAME END AS INT) AS col
  FROM   INFORMATION_SCHEMA.COLUMNS
  WHERE  TABLE_SCHEMA = @TableSchema
         AND TABLE_NAME = @TableName
         AND COLUMN_NAME NOT LIKE '%[^0-9]%'
  ORDER  BY col

  SET @N = @@ROWCOUNT

  IF @N = 0
      OR EXISTS(SELECT *
                FROM   @cols
                WHERE  idx <> col)
    BEGIN
        RAISERROR ('Incompatible table passed',16,1)
        RETURN
    END

  DECLARE @collist nvarchar(max)

  SELECT @collist = COALESCE(@collist + ',', '') + QUOTENAME(col)
  FROM   @cols  

DECLARE @dynsql nvarchar(max) = N'
WITH cte1
     AS (SELECT *,
                (SELECT ' + CAST(@N+1 AS VARCHAR(10)) + ' - COUNT(c)
                 FROM   (VALUES' + REPLACE(REPLACE(@collist,']','])'),'[','([') + ') T (c)) AS RN
         FROM   ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + '),
     cte2
     As (SELECT *
         FROM   cte1 UNPIVOT (data FOR col IN (' + @collist + ') ) AS unpvt),
     cte3
     As (SELECT RN,
                data,
                col
         FROM   cte2
         UNION ALL
         SELECT CAST(col as int),
                data,
                RN
         FROM   cte2)
SELECT ' + @collist + '
FROM   cte3 
PIVOT( max (data) FOR col IN (' + @collist + ')) AS pvt'

IF @Debug = 1
    SELECT @dynsql as [processing-instruction(x)] FOR XML PATH 

EXEC (@dynsql)

示例用法

CREATE TABLE dbo.BaseData(
  [1] float,
  [2] float,
  [3] float,
  [4] float)

INSERT INTO dbo.BaseData
SELECT 1,12,13,14 UNION ALL
SELECT NULL,1,23,24 UNION ALL 
SELECT NULL, NULL, 1,34 UNION ALL 
SELECT NULL, NULL, NULL, 1;

GO

EXEC dbo.Transpose 'dbo', 'BaseData', 0