将行合并为列

admin

将行合并为列

sql

我遇到以下情况（严重抽象，请忽略不良设计）：

CREATE TABLE dbo.PersonTest (Id INT, name VARCHAR(255))

INSERT INTO dbo.PersonTest
        (Id, name )
VALUES  (1, 'Pete')
,       (1, 'Marie')
,       (2, 'Sam')
,       (2, 'Daisy')

我正在寻找以下结果：

Id  Name1   Name2
1   Marie   Pete
2   Daisy   Sam

因此，对于每个ID，应合并行。

获得此结果，我使用以下查询：

WITH PersonRN AS
(
    SELECT  *
    ,       ROW_NUMBER() OVER(PARTITION BY Id ORDER BY name) RN
    FROM    dbo.PersonTest
)

SELECT      PT1.Id
,           PT1.name Name1
,           PT2.name Name2
FROM        PersonRN AS PT1
LEFT JOIN   PersonRN AS PT2 -- Left join in case there's only 1 name
        ON  PT2.Id = PT1.Id
        AND PT2.RN = 2
WHERE       PT1.RN = 1

哪个工作得很好。

我的问题是：这是最好的方法（在性能和弹性方面是最好的）吗？例如，如果这些ID中的一个具有第三名，那么我的查询将忽略该第三名。我在想解决此问题的最佳方法是动态SQL，这很好，但是如果可以在没有动态的情况下完成，那么我更愿意这样做。

阅读 193

2021-07-01

共1个答案

admin

除了dynamic之外PIVOT，您还可以使用 Dynamic
Crosstab
来做到这一点，出于可读性的考虑，我更喜欢这样做。

SQL小提琴

DECLARE @sql1 VARCHAR(1000) = '',
        @sql2 VARCHAR(1000) = '',
        @sql3 VARCHAR(1000) = ''
DECLARE @max INT

SELECT TOP 1 @max = COUNT(*) FROM PersonTest GROUP BY ID ORDER BY COUNT(*) DESC

SELECT @sql1 = 
'SELECT
    ID' + CHAR(10)

SELECT @sql2 = @sql2 +
'   , MAX(CASE WHEN RN =' + CONVERT(VARCHAR(5), RN) 
    + ' THEN name END) AS ' + QUOTENAME('Name' + CONVERT(VARCHAR(5), RN)) + CHAR(10)
FROM(
    SELECT TOP(@max)
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RN
    FROM sys.columns
)t
ORDER BY RN

SELECT @sql3 =
'FROM(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY name)
    FROM PersonTest
)t
GROUP BY ID
ORDER BY ID'

PRINT (@sql1 + @sql2 + @sql3)
EXEC (@sql1 + @sql2 + @sql3)

2021-07-01