SQL 服务器透视查询

Question

我在sql服务器中有以下数据：

Testnum Machine Name    Certification
1234    Computer 1  CCNA
2345    Computer 2  CNSE
2345    Computer 2  BBC
1234    Computer 2  CNBC
2345    Computer 3  J2EE

我希望它看起来像下面：

Testnum Machine Name    Certification   Certification   Certification   Certification
1234    Computer 1  CCNA    CNBC        
2345    Computer 2  CNSE    BBC J2EE    

有人可以帮我吗，我尝试了各种透视，但由于我无法在此数据集中使用任何聚合函数，因此很难设置它。

谢谢

Answer 1

除了PIVOT之外，您还可以通过使用ROW_NUMBER()来获得所需的结果：

;WITH cte AS (SELECT *,'Certification'+CAST(ROW_NUMBER() OVER(PARTITION BY Testnum ORDER BY MachineName) AS VARCHAR(5))RN
              FROM Table1)
SELECT Testnum
      ,MIN(Machinename)MachineName
      ,MAX(Certification1)Certification1
      ,MAX(Certification2)Certification2
      ,MAX(Certification3)Certification3
FROM cte
PIVOT (MAX(Certification) FOR RN IN (Certification1,Certification2,Certification3))p
GROUP BY TestNum

演示：SQL 小提琴

动态版本将如下所示，但目前无法测试：

DECLARE @cols AS VARCHAR(MAX)
       ,@sql  AS VARCHAR(MAX)
SET @cols = STUFF((SELECT DISTINCT ',' + 'Certification'+CAST(ROW_NUMBER() OVER(PARTITION BY Testnum ORDER BY MachineName) AS VARCHAR(5))RN 
                   FROM Table1 c
                   FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') 
                   ,1,1,'') 
SET @sql = 'WITH cte AS (SELECT TestNum,Certification,MachineName) AS VARCHAR(5))RN
                         FROM Table1
                          )
            SELECT *
            FROM cte
            PIVOT (MAX(Certification) FOR RN IN ('+@cols+'))p
            '
EXEC (@sql)

Answer 2

嗨，

我们也可以使用最大条件

DECLARE @Table1  TABLE 
    ([Testnum] int, [MachineName] varchar(10), [Certification] varchar(4))
;
INSERT INTO @Table1
    ([Testnum], [MachineName], [Certification])
VALUES
    (1234, 'Computer 1', 'CCNA'),
    (2345, 'Computer 2', 'CNSE'),
    (2345, 'Computer 2', 'BBC'),
    (1234, 'Computer 2', 'CNBC'),
    (2345, 'Computer 3', 'J2EE')
;
select Testnum,MachineName,MAX(case WHEN MachineName = 'Computer 1' THEN d.Certification END)As Certification1,
MAX(case WHEN MachineName = 'Computer 2' THEN Certification END)As Certification2,
MAX(case WHEN MachineName = 'Computer 3' THEN Certification END)As Certification3 from
(Select t.Testnum,t.MachineName,Certification,ROW_NUMBER()OVER(partition by t.MachineName order by t.Testnum )as seq
from @Table1 t
 ) d
GROUP BY Testnum,MachineName,seq