Selecting Values From a table as Column Headers

Question

I have a table 'propertyvalues' as follows:

ID  FileID  Property  Value

 1   x       Name      1.pdf

 2   x       Size      12567

 3   x       Type      application/pdf

 4   y       Name      2.pdf

 5   y       Size      23576

 6   y       Type      application/pdf

......

and so on

How to write a SQL query on the table above to fetch a result like below

 
FileID  Name     Size      Type

  x     1.pdf    12567     application/pdf

  y     2.pdf    23576     application/pdf

Answer 1

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) Type
from yourtable
group 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
) x
pivot
(
  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)

Answer 2

A version with joins that works regardless of missing rows:

SELECT  
    pd.FileID 
  , p1.Value  AS Name
  , p2.Value  AS Size
  , p3.Value  AS Type
FROM
        ( 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 JOIN version 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 PIVOT version.

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.

Answer 3

select 
  p1.FileID as FileID,
  p1.Value as Name,
  p2.Value as Size,
  p3.Value as Type
from 
  propertyvalues as p1 join 
  propertyvalues as p2 on p1.FileID = p2.FileID join 
  propertyvalues as p3 on p1.FileID = p3.FileID
where
  p1.Property='Name' AND p2.Property='Size' AND p3.Property='Type'

Answer 4

Create function [dbo].[AF_TableColumns](@table_name nvarchar(55))
returns nvarchar(4000) as
begin
declare @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 DESC
return @str
end

--select dbo.AF_TableColumns('YourTable') Select * from YourTable