T-SQL Order By on key-value table

I have two tables like :

Docs {#DocId, SomeData} KeyValues {#RowId, DocId, Name, Value}

and it have values like :

Docs

D1 | This is content of doc 1

D2 | This is content of doc 2

Key-values

1 | D1 | Firstname | David

2 | D1 | Lastname  | Andersson

3 | D2 | Firstname | Anders

4 | D2 | Lastname  | Andersson

I would like to get Doc.SomeData and order by the value of Firstname

How-would you do it? Pivot? Joins?

EDITED I might have simplified to much. The KeyValue table has the value divided into several columns, but the actual column to get the value is defined in C# code where the SQL is generated on the fly:

KeyValues {#RowId, DocId, Name, StringValue, IntegerNumericValue, FractalNumericValue, BoolValue, GuidValue, DateTimeValue}

Using Left Joins would need me to issue one left join for each sorting expression otherwise the set might get dupli开发者_StackOverflowcation of docs in the result.

---

Try this :

SELECT SomeData 
FROM Doc d
INNER JOIN KeyValues k ON d.DocId = k.DocId AND k.Name = 'FirstName'
ORDER BY k.Value

UPDATE : If your sorting needs dynamically based on values passed from c# code then you can use case in order by clause like :

SELECT SomeData 
FROM Doc d
INNER JOIN KeyValues k ON d.DocId = k.DocId AND k.Name = @PassedValue
ORDER BY CASE WHEN @PassedValue = 'FirstName' THEN k.StringValue
              WHEN @PassedValue = 'LastName' THEN k.StringValue
              WHEN @PassedValue = 'Date' THEN k.DateTimeValue
              ELSE k.StringValue 
              END

---

SELECT Doc.SomeData, KV.Value
FROM Doc JOIN KeyValues KV ON Doc.DocId = KV.DocId
WHERE KV.Name = 'Firstname'
ORDER BY KV.Value

---

Look in the comments to the Gist for the solution. Any tweaks are highly appreciated.

https://gist.github.com/1343449#comments