tsql - 如何在 SQL Server 中转置上三角矩阵

Question

如果我有一个表(上三角矩阵)，数据类型是 float ，例如:

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 中可能吗？

Answer 1

这是一种方法。

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