您没有指定 RDBMS,如果您知道要转换的列数,则可以对值进行硬编码:
select FileId,
max(case when property = 'Name' then value end) Name,
max(case when property = 'Size' then value end) Size,
max(case when property = 'Type' then value end) Type
from yourtable
group by FileId
这基本上是一个PIVOT
函数,一些 RDBMS 会有一个PIVOT
,如果您这样做,那么您可以使用以下内容,PIVOT
在 SQL Server、Oracle 中可用:
select *
from
(
select FileId, Property, Value
from yourTable
) x
pivot
(
max(value)
for property in ([Name], [Size], [Type])
) p
如果要转换的列数未知,则可以使用动态PIVOT
。这获取要在运行时转换的列的列表:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(property)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ' + @cols + ' from
(
select FileId, Property, Value
from yourtable
) x
pivot
(
max(value)
for Property in (' + @cols + ')
) p '
execute(@query)