sql - SQL数据行作为列

Question

我想返回代表我们数据库一部分的CSV文件的各个用户的列。

基本表如下所示:

ProviderId ProviderName
1 Test
2 FooBar

UserId UserName
1 Mike
2 Bob
3 John

ProviderId UserId
1 1
2 2
1 3

我希望结果看起来像这样:

ProviderName UserName1 UserName2 ...
Test Mike John
FooBar Bob

到目前为止，我尝试为此使用数据透视，但是它仅支持Ints(因此仅支持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

但是，我需要能够从用户子行中删除用户名(以及其他文本详细信息)，并将其作为每个提供者父级的列追加。

对这项工作有什么建议吗？

谢谢

Answer 1

好的，如果您想获取动态的列数，则必须使用动态sql。为此，请首先查看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