Help with Static Crosstab Query

[jonshin]

Using pubs I need to find titles that have exactly 2 authors and display the results like this

[title][author1][author2]
title1 author1 author2

I have a query that returns in this format

[title][author]
title1 author1
title1 author2

select t.title, a.au_lname from titles t
inner join titleauthor ta on t.title_id = ta.title_id
inner join authors a on ta.au_id = a.au_id
where t.title_id in(select title_id from titleauthor
group by title_id
having count(au_id) = 2)


I don't want to use dynamic sql, cursors. It must be pure sql.

Thanks,
Jon

 

[zhavic]

For example:

select t1.title, a1.au_lname, a2.au_lname
from ( select title_id, min( au_id ) as au_id from titleauthor group by title_id having count(au_id) = 2 ) AS double_titles
inner join titles t1 on t1.title_id = double_titles.title_id
inner join titleauthor ta1 on t1.title_id = ta1.title_id and ta1.au_id = double_titles.au_id
inner join authors a1 on ta1.au_id = a1.au_id
inner join titleauthor ta2 on ta2.title_id = double_titles.title_id and ta2.au_id <> double_titles.au_id
inner join authors a2 on ta2.au_id = a2.au_id



Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[jonshin]

Exactly what I needed! Thanks Zhavic