yet another tsql question

Question

i have three tables

documents
attributes 
attributevalues

documents can have many attributes and these atributes have value in attributevalue table

what i want in single query get all documents and assigned atributes of relevant documents in row each row (i assume every documents have same attributes assigned dont need complexity of diffrent attribues now)

for example

docid  attvalue1  attvalue2

1         2         2

2         2         2

3         1         1

how can i do that in single query

Answer 1

If you know all the attributes (and their IDs) at design-time:

SELECT d.docid,
       a1.attvalue AS attvalue1
       a2.attvalue AS attvalue2
  FROM documents d
  JOIN attributevalues a1 ON d.docid = a1.docid
  JOIN attributevalues a2 ON d.docid = a2.docid
 WHERE a1.attrid = 1
   AND a2.attrid = 2

If you don't, things get quite a bit messier and difficult to answer without knowing your schema.

Answer 2

Off the top if my head, I don't think you can do this without dynamic SQL.

The crux of the Entity-Attribute-Value (EAV) technique (which is what you are using) is to store columns as rows. What you want to do is convert those rows back to columns for the purpose of this query. Using PIVOT makes this possible. However, PIVOT requires knowing the number of rows that need to be converted to columns at the time the query is written. So assuming you are using EAV because you need flexible attributes/values, you won't know this information when you write the query.

So the solution would be to use dynamic SQL in conjunction with PIVOT. Did a quick search and this looks promising (didn't really read the whole thing):

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

For the record, I am not a fan of dynamic SQL and would recommend finding another approach to the larger problem (e.g. pivoting in application code).

Answer 3

lets make example

documents table's columns

docid,docname,createddate,createduser

and values

1 account.doc 10.10.2010 aeon
2 hr.doc 10.11.2010 aeon

atributes table's columns

attid,name,type

and values

1 subject string
2 recursive int

attributevalues table's columns

attvalueid,docid,attid,attvalue(sql_variant)

and values

1 1 1 "accounting doc"
1 1 2 0
1 2 1 "humen r doc"
1 2 2 1

and I want query result

docid,name,atribvalue1,atribvalue1,atribvalueN 
1 account.doc "accounting doc" 0
2 hr.doc "humen r doc" 1