Ankit Khatri Ankit Khatri - 1 year ago 84
SQL Question

Selecting Values From a table as Column Headers

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 Source

A version with joins that works regardless of missing rows:

  , p1.Value  AS Name
  , p2.Value  AS Size
  , p3.Value  AS Type
          FROM propertyvalues 
        ) AS pd
        propertyvalues AS p1
            ON  p1.FileID = pd.FileID 
            AND p1.Property = 'Name'
        propertyvalues AS p2
            ON  p2.FileID = pd.FileID 
            AND p2.Property = 'Size'
        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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download