sql - 使用 Pivot 将行转换为动态列

Question

我在单表中有以下有关房地产数据的信息：

PropertyID
OwnerName
OwnershipDate
PropertyAddress

您可以在此处找到示例数据

我想pivot将结果转换为所需的输出，如下所示

PropertyID      OwnerName     OwnershipDate                     OwnerName      OwnershipDate
0121320100011   RIVERA VICTOR   2018-02-06 00:00:00.0000000    RIVERA RAQUEL   2018-02-06 00:00:00.0000000

我希望列数根据每个PropertyID.

迄今为止所做的努力如下：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.OwnerName) 
            FROM owners_rec c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT   ' + @cols + ' from 
            (
                select   account,ownername
                from owners_rec
           ) x
            pivot 
            (
                 max(account)
                for ownername in (' + @cols + ')
            ) p '


execute(@query)

伸出援助之手总是受欢迎的:)

标签： sqlsql-serverpivotpivot-tabledynamic-sql