July 19, 2011 at 4:28 am
Hi,
I wish to write a query to return a list of all authors and the titles they have written. Desired output would look like this:
...
Albert Ringer_____Is Anger the Enemy?
________________Life Without Fear
Ann Dull_________Secrets of Silicon Valley
Anne Ringer______Is Anger the Enemy?
________________The Gourmet Microwave
Burt Gringlesby____Sushi, Anyone?
....
I managed to write a query that almost achieves this. The problem being that author names are repeated. Can my query be modified using a group by clause or similar to achieve the above output? (underscores used to create a table look)
select distinct(au_fname + ' ' + au_lname) AS Author, title
from authors inner join titleauthor
on authors.au_id = titleauthor.au_id inner join titles
on titleauthor.title_id = titles.title_id
returns:
...
Albert Ringer____Is Anger the Enemy?
Albert Ringer____Life Without Fear
Ann Dull________Secrets of Silicon Valley
Anne Ringer_____Is Anger the Enemy?
Anne Ringer_____The Gourmet Microwave
Burt Gringlesby___Sushi, Anyone?
...
thanks in advance for any help
Raoul
July 19, 2011 at 7:39 am
Welcome to SSC. Notice how i posted some sample ddl and data? Check out the link in my signature for best practices on posting questions.
You can achieve this doing something like below.
create table #books
(
Author varchar(50),
title varchar(50)
)
insert #books values ('Albert Ringer', 'Is Anger the Enemy?'), ('Albert Ringer', 'Life Without Fear'),
('Ann Dull', 'Secrets of Silicon Valley'), ('Anne Ringer', 'Is Anger the Enemy?'),
('Anne Ringer', 'The Gourmet Microwave'), ('Burt Gringlesby', 'Sushi, Anyone?')
;with cte as
(
select *, ROW_NUMBER() over(partition by author order by author, title) as RowNum , ROW_NUMBER() over(order by author, title) as SortOrder
from #books
)
select case when RowNum = 1 then Author else '' end as Author, title
from cte
order by SortOrder
There may be a better way to accomplish this but this is pretty straight forward.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply