Table rows with identifying parameter in each row SQL SERVER 2008 into single row

Question

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

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

pageID    key            value    

201       title          Page 201's title
201       description    This is 201
201       author         Dave
301       title          Page 301's title
301       description    This is 301
301       author         Bob         

As you've probably guessed, what I need is a query that will produce:

pageID   title              description        author

201      Page 201's title   This is page 201   Dave
301      Page 301's title   This is page 301   Bob

If anybody could help, i'd be eternally grateful - I know this is "please send me the code" but I'm absolutely stuck.

Thanks in advance.

Answer 1

Quick hack may 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'

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