0

I have 5 tables:Documents, DocumentTypes, Keywords, DocumentType_Keywords and Document_Keywords.
Keywords schema is: 

Id | Name

DocumentTypes schema is:

Id | Name

DocumentType_Keywords schema is:

Id | DocumentType_Id | Keyword_Id

Documents schema is:

Id | Name | DocumentType

Document_Keywords schema is: 

Id | Keyword_id | Document_Id | Value

In plain English, a document has many keywords, according to it's Document_Type.

As you can imagine I have a unknow number of keywords at compile time, but I would like to have a query that outputs: 

Document Name | Keyword Named 1 | Keyword Named 2 | ... | Keyword Named N  
My Doc 1        Original          Leo               ...   Hey!
My Doc 2        NULL              David             ...   NULL
My Doc 3        NULL              NULL              ...   NULL
My Doc 4        Not Original      Kevin             ...   China

Pls note that there is no summarization here

Rigel1121
  • 2,022
  • 1
  • 17
  • 24
Leonardo
  • 10,737
  • 10
  • 62
  • 155
  • Although this example has sums, it's still basically the same thing: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – James Z Feb 20 '15 at 17:08
  • @JamesZ actually I found out that the 'max' is a key point in it... – Leonardo Feb 20 '15 at 18:31
  • The dynamic pivot trick JamesZ pointed out will work for your situation. Yes `Pivot` needs an aggregate function like `max` but as long as their either is none or 1 value for specific cell that won't matter. – MWillemse Feb 20 '15 at 20:32

0 Answers0