sql-server - 在 SQL Server 中使用动态查询进行透视

Question

脚本

DECLARE @Names AS TABLE(ID INT, Name VARCHAR(MAX))

INSERT INTO @Names 
VALUES (1, 'Paul'), (2, 'John'), (3, 'Tayler');

DECLARE @PersonInfo AS TABLE 
                       (
                           ID INT,
                           NameID INT,
                           Subject VARCHAR(100),
                           Marks FLOAT
                       )

INSERT INTO @PersonInfo 
VALUES (1, 1, 'Maths', 95.34), (2, 2, 'Science', 32.12),
       (3, 3, 'History', 23.21), (4, 2, 'Maths', 32.4),
       (5, 3, 'Science', 60.34), (6, 1, 'Music', 60.23);

SELECT * FROM @PersonInfo

我尝试使用PIVOT表达式生成动态列，但没有运气。谁能给我一个想法或解决方案来生成以下输出。提前致谢

预期的解决方案：

Subject     Paul    John    Tayler
-----------------------------------
Maths       95.34   32.4    
Science             32.12   60.34
History                     23.21
Music       60.23

标签： sql-servertsqlpivot

Answer 1

使用PIVOT您可以达到预期的结果：

;WITH Result AS (
   SELECT
      P.Subject, N.Name, P.Marks
   FROM @PersonInfo P
   INNER JOIN @Names N ON N.ID = P.NameID 
)
SELECT *
FROM Result
PIVOT (Max(Marks) FOR Name IN (Paul, John, Tayler)) R

db<>fiddle 上的演示