SQL Server: Joining arbitrary number of columns

I have a products table, a pricelists table, and a pricelist_products table that connects them. There are an arbitrary number of pricelists and pricelists do not cover all products. I'm looking to select all products and have the prices from pricelist_products as n columns in the result. Is this possible (without procedures)? I've come across pivot queries, but the arbitrary number of lists doesn't seem to fit there.

example schema:

products
pid | name
开发者_开发百科----|------
1   | foo
2   | bar
3   | thing

pricelists
lid | name
----|------
1   | internal
2   | external
n   | ...

pricelist_products
lid | pid | price
----|-----| ------
1   | 1   | 1.00
1   | 2   | 2.00
2   | 1   | 3.00
2   | 3   | 4.00

desired result:

pid name internal external ...n
1   foo   1.00     3.00
2   bar   2.00     -
3   thing -        4.00

You will have to resort to dynamic SQL for this. There are numerous examples how to do it, these are just first two that I clicked:

http://www.sqlprof.com/blogs/sqldev/archive/2008/04/12/pivots-with-dynamic-columns-in-sql-server-2005-2008.aspx

http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx