SQL Data Rows as Columns

Question

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 ORDER 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

possible duplicate of [Using PIVOT in SQL Server](http://stackoverflow.com/questions/2696288/using-pivot-in-sql-server) — JNK, Apr 18 '11 at 18:03
There are about 12 billion questions about PIVOT on here. Please take a look at search before posting a new one... — JNK, Apr 18 '11 at 18:03
@JNK - true, there are plenty PIVOT questions here but the one you link to doesn't answer OP's question. The amount of columns is not fixed as it is in the linked answer and it must be said, OP *did* made and effort in trying to solve this for himself. — Lieven Keersmaekers, Apr 18 '11 at 18:11
@Lieven - I'm not searching through all the `PIVOT` questions on here for the right answer, I'm pretty sure that responsibility is on the asker. If he looked he would find it, though. — JNK, Apr 18 '11 at 18:12
@Chris, you might be helped though with following answer: http://stackoverflow.com/questions/1439403/sql-server-dynamic-pivot-table-sql-injection — Lieven Keersmaekers, Apr 18 '11 at 18:13

score 1 · Accepted Answer · answered Apr 18 '11 at 18:15

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

Incidentally, I wasn't looking for dynamic columns (the columns and number I need are known and fixed) but how to pull text values out of a Pivot, and your MIN(UserName) answered my question. Thanks! — , Apr 18 '11 at 19:15

SQL Data Rows as Columns

1 Answers1