RE: Select statement

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 10/26/04 

Date: Tue, 26 Oct 2004 09:43:02 -0700

This may help:

SELECT T.title_id, T.title,
 MAX(CASE WHEN U.au_ord=1 THEN A.au_fname+' '+A.au_lname ELSE '' END)+
 MAX(CASE WHEN U.au_ord=2 THEN ', '+A.au_fname+' '+A.au_lname ELSE '' END)+
 MAX(CASE WHEN U.au_ord=3 THEN ', '+A.au_fname+' '+A.au_lname ELSE '' END)+
 MAX(CASE WHEN U.au_ord=4 THEN ', '+A.au_fname+' '+A.au_lname ELSE '' END)
  AS authors
 FROM Pubs..titles AS T
 JOIN Pubs..titleauthor AS U
  ON T.title_id = U.title_id
 JOIN Pubs..Authors AS A
  ON A.au_id = U.au_id
 GROUP BY T.title_id, T.title 

-- 
David Portas
SQL Server MVP
--
Loading