在 SQL Server 中的单个列/行中获取多个值

赛斯里

我有一个表projects，其中包含 projectid 和 projectname。每个项目都有多个用户，表是project_users，字段是projectuserid, projectid。

此外，每个项目都有多个解决方案，表格是project_solutions，字段是projectsolutionid, projectid。

同样，每个解决方案都有多个用户，这些用户存储在表中solution_users，字段是solutionuserid, solutionid, userid

有主表在这里users与领域的用户userid, username，并solutions与领域的解决方案solutionid, solutionname。

我试图在单行中获取输出，其中第一列将是projectid，第二列是各自users of the project in an array or json format的第三列solutions for the project，第四列是solution users

表格如下

项目

 projectid      projectname
    1             abc
    2             xyz

用户

 userid      username        image(data type image)
    1          user1         04949949499994
    2          user2         3434jj34kjd3434
    3          user3         8934u34kj343434

解决方案

 solutionid      solution_name
     1               sol1
     2               sol2
     3               sol3

项目用户

  id     projectuserid        projectid
   1           1                  1
   2           2                  1
   3           3                  2

项目解决方案

 id            solutionid       projectid
  1                1                1
  2                3                1
  3                2                2

project_solution_users

  id       projectsolutionid         userid
   1             1                      1
   2             1                      2
   3             2                      1
   4             2                      2
   5             2                      3

我期待的输出如下

 {
  projectid:1
  users: [user1, user2],
  solutions: [ sol1, sol3 ],
  user_images: [04949949499994, 3434jj34kjd3434]
 }
 {
  projectid:2
  users: [user3],
  solutions: [ sol2 ],
  user_images: [04949949499994, 3434jj34kjd3434, 8934u34kj343434]
  }

这是我试过的

    select p.projectid, ps.solutionid, cast(cast (uu.image as varbinary) as varbinary) as userimage,
    from projects as p
    left join project_users as pu on pu.projectid = p.projectid
    left join users as u on u.userid = su.userid
    left join project_solutions as ps on ps.projectid = p.projectid
    left join solutions as s on s.solutionid = ps.solutionid
    left join project_solution_users as su on su.projectsolutionid = ps.solutionid
    left join users as uu on uu.userid = su.userid
    group by p.projectid, s.solution_name, cast(cast (uu.image as varbinary) as varbinary)
    for json path, without_array_wrapper

但它会生成多行。

如何使用 SQL Server 实现这一点？我曾尝试使用内部连接和子查询，但我卡住了，没有得到相应的输出。

马克西姆·佐洛坚科

select  t1.projectid,
        username,
        user_image,
        solution_name
from    dbo.project_solutions t1
left join dbo.project_users t2 on t2.projectid = t1.projectid
left join dbo.users t3 on t3.userid = t2.projectuserid
left join dbo.solutions t4 on t4.solutionid = t1.solutionid
for json auto

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。