SQL Query Trouble

Question

I have a scenario where I need to convert rows to columns. I likely need to use PIVOT, but I haven't been able to figure this one out.

Here is a simplified version of what I have: Table: ContentAuthor

ContentID | AuthorName
----------------------
1         | Joe
1         | John
1         | Mary
2         | John
2         | Dan
3         | Sam

What I would like to get:

ContentID | Author1  | Author2  | Author3  | Author4  | Author5
-----------------------------------------------------------------
1         | Joe      | John     | Mary     |          |
2         | John     | Dan      |          |          |
3         | Sam      |          |          |          |

At this point I'm not really concerned with the order they show up.

Any help would be greatly appreciated.

Answer 1

This will work for up to 5 authors per ContentId if you need to allow for arbitrary amounts then you will need to use dynamic SQL

;WITH ContentAuthor(ContentID, AuthorName) AS
(
SELECT 1,'Joe' UNION ALL
SELECT 1,'John' UNION ALL
SELECT 1,'Mary' UNION ALL
SELECT 2,'John' UNION ALL
SELECT 2,'Dan' UNION ALL
SELECT 3,'Sam'
),T AS
(
 SELECT *,
        ROW_NUMBER() OVER (PARTITION BY ContentID ORDER BY (SELECT 0)) RN
 FROM ContentAuthor
)
SELECT ContentID,
       [1] AS Author1,
       [2] AS Author2,
       [3] AS Author3,
       [4] AS Author4,
       [5] AS Author5
FROM T
PIVOT(MAX(AuthorName) FOR RN IN ([1],[2],[3],[4],[5])) AS Pvt;