SQL Data Rows as Columns

I am wanting to return columns representing various users for a CSV file for part of our database.

The base tables look something like this:

ProviderId ProviderName
1 Test
2 FooBar

UserId UserName
1 Mike
2 Bob
3 John

ProviderId UserId
1 1
2 2
1 3

I want the results to look like this:

ProviderName UserName1 UserName2 ...
Test Mike John
FooBar Bob

So far, I tried using pivot for this, but it only supports Ints (and thus only the Ids):

SELECT ProviderId, [1],[2],[3],[4] FROM (
    SELECT p.ProviderId, u.UserId, ROW_NUMBER() OVER(PARTITION BY p.ProviderId OR开发者_JAVA技巧DER BY p.ProviderId ASC, u.UserId ASC) as Row
        FROM Provider p
        left join ProviderUser pu on p.ProviderId=pu.ProviderId
        left join [User] u on pu.UserId = u.UserId
) as MyTable
PIVOT
(
SUM(UserId) FOR Row IN ([1],[2],[3],[4])
) as PivotTable


ProviderId  1   2   3   4
1   2   15  18  22
2   17  23  NULL    NULL

However, I need to be able to take the usernames (and other text details) out of user child rows and append them as columns per their provider parent.

Any suggestions for making this work?

Thanks

Ok, if you want to get a dynamic number of columns, then you are gonna have to use dynamic sql. For that, please take a look at this link first. Then, you can try this:

DECLARE @Users NVARCHAR(MAX), @Query NVARCHAR(MAX)
SET @Users = ''

SELECT @Users = @Users + '[UserName' + CAST(UserId AS VARCHAR) +'],'
FROM Users
ORDER BY UserId

SET @Users = LEFT(@Users,LEN(@Users)-1)

SET @Query = '
SELECT ProviderName, '+@Users+'
FROM (  SELECT B.ProviderName, C.UserName, ''UserName''+CAST(A.UserId AS VARCHAR) UserAlias
        FROM ProviderUsers A
        JOIN Provider B
        ON A.ProviderId = B.ProviderId
        JOIN Users C
        ON A.UserId = C.UserId) T
PIVOT(MIN(UserName) FOR UserAlias IN ('+@Users+')) AS PT'

EXEC sp_executesql @Query