我修改了Taryn的答案(“Pivot with unknown number of columnnames”版本),以显示结果中超过1行。这需要有一个额外的“Group”列。
DROP TABLE #yourtable
CREATE table #yourtable
([Id] int,[Group] int, [Value] varchar(6), [ColumnName] varchar(13))
;
INSERT INTO #yourtable
([Id],[Group], [Value], [ColumnName])
VALUES
(1,1, 'John', 'FirstName'),
(2,1, '2.4', 'Amount'),
(3,1, 'ZH1E4A', 'PostalCode'),
(4,1, 'Fork', 'LastName'),
(5,1, '857685', 'AccountNumber'),
(6,2, 'Pedro', 'FirstName'),
(7,2, '5.1', 'Amount'),
(8,2, '123456', 'PostalCode'),
(9,2, 'Torres', 'LastName'),
(10,2, '857686', 'AccountNumber')
;
;
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName)
from #yourtable
group by [Group], ColumnName, id
having [group] = (SELECT TOP 1 MIN([Group])FROM #yourtable)
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT ' + @cols + N' from
(
select value, ColumnName,[Group]
from #yourtable
GROUP BY [Group],ColumnName,Value
) x
pivot
(
max(value)
for ColumnName in (' + @cols + N')
) p '
exec sp_executesql @query;
![enter image description here](https://istack.dev59.com/57PjA.webp)
[First.Last]
来转义点号。 - Taryn