sql-server - 我如何旋转表格？

Question

我有一个动态的单行表，例如:

PersonId|FirstName|LastName|Address|PhoneNumber
-----------------------------------------------
1        Anuj      Tamrakar  NY     +525418

我想旋转这个表并希望在临时表中输出如下:

PersonalDetails|Value
----------------------
PersonId        1
FirstName       Anuj
LastName        Tamrakar
Address         NY
PhoneNumber     +525418

第一个表是动态单行临时表。对于这个例子，我有 5 列。根据我的标准，我可能会有更多或更少的列

Answer 1

你实际上想要UNPIVOT :

SELECT  PersonalDetails, Value
FROM 
   (SELECT CAST([PersonId] AS VARCHAR(MAX)) AS [PersonId], 
           CAST([FirstName] AS VARCHAR(MAX))  AS [FirstName],
           CAST([LastName] AS VARCHAR(MAX)) AS [LastName],
           CAST([Address] AS VARCHAR(MAX)) AS [Address],
           CAST([PhoneNumber] AS VARCHAR(MAX)) AS [PhoneNumber]
   FROM mytable) p
UNPIVOT
   (Value FOR PersonalDetails IN 
      ([PersonId], [FirstName], [LastName], [Address], [PhoneNumber])
) AS unpvt;

所有'to-be-unpivoted'字段必须是相同类型，因此使用CAST。

Demo here

对于动态列数，您必须使用动态 sql:

DECLARE @cols VARCHAR(MAX) = ''
DECLARE @cast_cols VARCHAR(MAX) = ''
DECLARE @qry VARCHAR(MAX)

SELECT @cols = @cols + ',[' + COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'mytable' AND TABLE_SCHEMA='dbo'

SELECT @cast_cols = @cast_cols + ',CAST([' + COLUMN_NAME + '] AS VARCHAR(MAX)) AS [' + COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'mytable' AND TABLE_SCHEMA='dbo'

SET @cols = STUFF(@cols, 1, 1, '')
SET @cast_cols = STUFF(@cast_cols, 1, 1, '')    

SET @qry = 'SELECT  PersonalDetails, Value FROM (' 
           + @cast_cols +        
          'FROM mytable) p
           UNPIVOT
           (Value FOR PersonalDetails IN (' + @cols + ')
           ) AS unpvt'
EXEC (@qry)