Table rows with an identification parameter in each row of SQL SERVER 2008 in one row

Question

Sorry - my question title is probably just as inept at my attempt to do this.

I have the following (well, similar) in a table in CMS

pageID key value 201 title Page 201 title 201 description This is 201 201 author Dave 301 title Page 301 title 301 description This is 301 301 author Bob 

As you probably guessed, I need a query that will produce:

 pageID title description author 201 Page 201 title This is page 201 Dave 301 Page 301 title This is page 301 Bob 

If anyone can help, I will be eternally grateful - I know this is “please send me the code”, but I am absolutely stuck.

Thanks in advance.

Answer 1

A quick hack can be

 select a.pageID, a.value as Title, b.value as Description, c.value as Author from Table a left outer join Table b on a.pageID = b.pageID and b.key = 'description' left outer join Table c on a.pageID = c.pageID and c.key = 'author' where a.key = 'title' 

It may not be the best solution, but it may work for you.

Answer 2

 Select PageId , Min( Case When key = 'title' Then Value End ) As Title , Min( Case When key = 'description' Then Value End ) As Description , Min( Case When key = 'author' Then Value End ) As Author From Table Group By PageId