sql - 如何使用 PIVOT 在 Select 查询中动态声明列

Question

我正在编写查询以获取 PersonID 的地址。以下查询对我有用，但它只返回两个地址。我想用一个查询处理“n”个地址。有什么办法吗？

非常感谢

SELECT 
  PersonID, PersonName
  [Address1], [Address2]
FROM
  (
  SELECT 
    P.PersonID, 
    P.PersonName, 
    (ROW_NUMBER() OVER(PARTITION BY P.PersonID ORDER BY A.AddressID)) RowID
    FROM tblPerson
    INNER JOIN tblAddress AS A ON A.PersonID = P.PersonID
  ) AS AddressTable
PIVOT
  (
    MAX(AddressID)
    FOR RowID IN ([Address1], [Address2])
  ) AS PivotTable;

Answer 1

假设有以下表格和示例数据:

USE tempdb;
GO

CREATE TABLE dbo.tblPerson(PersonID INT, PersonName VARCHAR(255));

INSERT dbo.tblPerson SELECT 1, 'Bob'
           UNION ALL SELECT 2, 'Charlie'
           UNION ALL SELECT 3, 'Frank'
           UNION ALL SELECT 4, 'Amore';

CREATE TABLE dbo.tblAddress(AddressID INT, PersonID INT, [Address] VARCHAR(255));

INSERT dbo.tblAddress SELECT 1,1,'255 1st Street'
            UNION ALL SELECT 2,2,'99 Elm Street'
            UNION ALL SELECT 3,2,'67 Poplar Street'
            UNION ALL SELECT 4,2,'222 Oak Ave.'
            UNION ALL SELECT 5,1,'36 Main Street, Suite 22'
            UNION ALL SELECT 6,4,'77 Sicamore Ct.';

以下查询获取您想要的结果，并显示它如何处理 0、1 或 n 个地址。在这种情况下，最高数字是 3，但如果您愿意，可以通过稍微调整示例数据来使用更多地址。

DECLARE @col   NVARCHAR(MAX) = N'',
        @sel   NVARCHAR(MAX) = N'',
        @from  NVARCHAR(MAX) = N'',
        @query NVARCHAR(MAX) = N'';

;WITH m(c) AS
(
    SELECT TOP 1 c = COUNT(*)
    FROM dbo.tblAddress
    GROUP BY PersonID
    ORDER BY c DESC
)
SELECT @col = @col + ',[Address' + RTRIM(n.n) + ']',
    @sel = @sel + ',' + CHAR(13) + CHAR(10) + '[Address' + RTRIM(n.n) + '] = x' 
        + RTRIM(n.n) + '.Address',
    @from = @from + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN xMaster AS x' 
        + RTRIM(n.n) + ' ON x' + RTRIM(n.n) + '.PersonID = p.PersonID AND x' 
        + RTRIM(n.n) + '.rn = ' + RTRIM(n.n)
FROM m CROSS JOIN (SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id]) 
    FROM sys.all_columns) AS n WHERE n.n <= m.c;

SET @query = N';WITH xMaster AS 
(
  SELECT PersonID, Address, 
    rn = ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY Address) 
    FROM dbo.tblAddress
)
SELECT PersonID, PersonName' + @col
+ ' FROM
    (
        SELECT p.PersonID, p.PersonName, ' + STUFF(@sel, 1, 1, '')
 + CHAR(13) + CHAR(10) + ' FROM dbo.tblPerson AS p ' + @from + '
 ) AS Addresses;';

PRINT @query;
--EXEC sp_executesql @query;

如果您打印 SQL，您将看到以下结果:

;WITH xMaster AS 
(
  SELECT PersonID, Address, 
    rn = ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY Address) 
    FROM dbo.tblAddress
)
SELECT PersonID, PersonName,[Address1],[Address2],[Address3] FROM
    (
        SELECT p.PersonID, p.PersonName, 
 [Address1] = x1.Address,
 [Address2] = x2.Address,
 [Address3] = x3.Address 
FROM dbo.tblPerson AS p 
 LEFT OUTER JOIN xMaster AS x1 ON x1.PersonID = p.PersonID AND x1.rn = 1
 LEFT OUTER JOIN xMaster AS x2 ON x2.PersonID = p.PersonID AND x2.rn = 2
 LEFT OUTER JOIN xMaster AS x3 ON x3.PersonID = p.PersonID AND x3.rn = 3
 ) AS Addresses;

如果你执行它，你会看到:

我知道到达这里的查询是一个丑陋的困惑，但你的要求决定了它。按照我在评论中的建议返回一个逗号分隔的列表，或者让表示层处理旋转会更容易。