-1

If I want to get multiple author in single for each title in pubs database. Is it possible using pivot? the table are joined with this query 

SELECT CONCAT(a.au_fname,', ',a.au_lname) AS AUTHOR_NAME,
       t.title 
FROM titleauthor AS c
join authors AS a 
ON   a.au_id=c.au_id
join titles t
ON   t.title_id=c.title_id;

This shows multiple author with multiple title but I want to have each title and all the authors mentioned in one row

Diagram

Cup of Java
  • 1,769
  • 2
  • 21
  • 34
Jennifer
  • 9
  • 4
  • (1) Provide sample data and desired results. (2) Tag the question with the database you are using. – Gordon Linoff Sep 28 '16 at 22:15
  • 1
    What version of sql are you running? You need to specify the column names when using PIVOT. If your using SQL Server 2012 the STUFF function (with xml) would work nicely here – M O'Connell Sep 28 '16 at 22:16

1 Answers1

0

PIVOT only works when you know the columns beforehand.

With an unknown number of authors for a title, you could only generate a string column holding all names, e.g. 'Mark Twain, Robert Louis Stevenson'. Thus you'd always get just one column for all the names. This is a string aggregation and different DBMS have a different function for that (GROUP_CONCAT, LISTAGG, STRING_AGG etc.) and you'd group by title.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • these function are recognized in the sql server. – Jennifer Sep 28 '16 at 23:19
  • You want an answer for SQL Server? Then you should tag your question "sql-server" and possibly even the version you are using. (You should do so with every SQL question, by the way.) SQL-Server lacks a string aggregation function as described. You must simulate it with STUFF and XML PATH. There are probably many answers on how to do this in stackoverflow and other websites. Here is one: http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server – Thorsten Kettner Sep 29 '16 at 05:07