Selecting Values From a table as Column Headers
You did not specify RDBMS, if you know the number of columns to transform then you can hard-code the values:
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) Typefrom yourtablegroup by FileId
This is basically a
PIVOT function, some RDBMS will have a
PIVOT, if you do then you can use the following,
PIVOT is available in SQL Server, Oracle:
select *from ( select FileId, Property, Value from yourTable) xpivot( max(value) for property in ([Name], [Size], [Type])) p
If you have an unknown number of columns to transform, then you can use a dynamic
PIVOT. This gets the list of columns to transform at run-time:
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)
A version with joins that works regardless of missing rows:
SELECT pd.FileID , p1.Value AS Name , p2.Value AS Size , p3.Value AS TypeFROM ( SELECT DISTINCT FileID FROM propertyvalues ) AS pd LEFT JOIN propertyvalues AS p1 ON p1.FileID = pd.FileID AND p1.Property = 'Name' LEFT JOIN propertyvalues AS p2 ON p2.FileID = pd.FileID AND p2.Property = 'Size' LEFT JOIN propertyvalues AS p3 ON p3.FileID = pd.FileID AND p3.Property = 'Type' ;
If you have a table where
FileID is the primary key, you may replace the
DISTINCT subquery with that table.
Regarding efficiency, it depends on a lot of factors. Examples:
Do all FileIDs have rows with Name, Size and Type and no other properties (and your table has a clustered index on
(FileID, Property))? Then the
MAX(CASE...)version would perform quite well as the whole table would have to be scanned anyway.
Are there (many) more than 3 properties and a lot of FileIDs have no Name, Size and Type, then the
JOINversion would work well with an index on
(Property, FileID) INCLUDE (Value)as only this index data would be used for the joins.
Not sure how efficient is the
What I suggest though is to test the various versions with your data and table sizes, in your envirorment (version, disk, memory, settings, ...) before you select which one to use.
Create function [dbo].[AF_TableColumns](@table_name nvarchar(55))returns nvarchar(4000) asbegindeclare @str nvarchar(4000) select @str = cast(rtrim(ltrim(column_name)) as nvarchar(500)) + coalesce(' ' + @str , ' ') from information_schema.columns where table_name = @table_name group by table_name, column_name, ordinal_position order by ordinal_position DESCreturn @strend--select dbo.AF_TableColumns('YourTable') Select * from YourTable