SQL Server - 如何在列中显示主详细信息数据

发布于 2024-08-25 02:01:00 字数 301 浏览 4 评论 0原文

我有两个表，为了简洁起见，我们将它们称为 TableA 和 TableB。这是架构：

TableA
ID – int
Name varchar(50)

TableB
ID – int
TableA_Fk – int
Value varchar(50)

表 A 中的每条记录在表 B 中最多可以有 9 条记录。我希望能够以列形式检索数据：

TableA-Name, TableB-Value1, … TableB-Value9

这可以使用查询吗？谢谢！

原文

I have two tables, to be concise let’s call them TableA and TableB. This is the schema:

TableA
ID – int
Name varchar(50)

TableB
ID – int
TableA_Fk – int
Value varchar(50)

Each record in table A can have at most 9 records in table B. I want to be able to retrieve the data in a columnar form:

TableA-Name, TableB-Value1, … TableB-Value9

Is this possible using queries? Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不…忘初心 2024-09-01 02:01:00

您可以执行以下操作：

SELECT rank() OVER (ORDER BY tableA_FK) as rank, tableA_fk, value
INTO #temp
FROM TableB b
ORDER BY rank 


SELECT a.Name, 
       CASE WHEN t.rank = 1 THEN t.Value ELSE NULL END AS TableB-Value1,
       CASE WHEN t.rank = 2 THEN t.Value ELSE NULL END AS TableB-Value2,
       CASE WHEN t.rank = 3 THEN t.Value ELSE NULL END AS TableB-Value3,
       .... (etc.)
FROM TableA a
INNER JOIN #temp t ON a.Id = t.tableA_fk

您需要 Sql Server 2005 或更高版本。
抱歉，但我没有 Sql Server（或时间）来对此进行良好的测试。希望这能给您一个想法并有所帮助。

You could do something like:

SELECT rank() OVER (ORDER BY tableA_FK) as rank, tableA_fk, value
INTO #temp
FROM TableB b
ORDER BY rank 


SELECT a.Name, 
       CASE WHEN t.rank = 1 THEN t.Value ELSE NULL END AS TableB-Value1,
       CASE WHEN t.rank = 2 THEN t.Value ELSE NULL END AS TableB-Value2,
       CASE WHEN t.rank = 3 THEN t.Value ELSE NULL END AS TableB-Value3,
       .... (etc.)
FROM TableA a
INNER JOIN #temp t ON a.Id = t.tableA_fk

You need Sql Server 2005 or up.
Sorry, but I don't have Sql Server (or the time) to test this well. Hope this gives you an idea and helps.

回复收藏 0 原文

无戏配角 2024-09-01 02:01:00

您将需要一个 LEFT JOIN 和一个 PIVOT 表

回复收藏 0 原文

溺孤伤于心 2024-09-01 02:01:00

除了独立于 DBRM 之外，这应该可以做到。

SELECT A.Name
    , SUM(CASE WHEN B.Value = 1 THEN 1 ELSE NULL END) AS B_Value_1
    , SUM(CASE WHEN B.Value = 2 THEN 2 ELSE NULL END) AS B_Value_2
    , SUM(CASE WHEN B.Value = 3 THEN 3 ELSE NULL END) AS B_Value_3
    , SUM(CASE WHEN B.Value = 4 THEN 4 ELSE NULL END) AS B_Value_4
    , SUM(CASE WHEN B.Value = 5 THEN 5 ELSE NULL END) AS B_Value_5
    , SUM(CASE WHEN B.Value = 6 THEN 6 ELSE NULL END) AS B_Value_6
    , SUM(CASE WHEN B.Value = 7 THEN 7 ELSE NULL END) AS B_Value_7
    , SUM(CASE WHEN B.Value = 8 THEN 8 ELSE NULL END) AS B_Value_8
    , SUM(CASE WHEN B.Value = 9 THEN 9 ELSE NULL END) AS B_Value_9
FROM A
    INNER JOIN B ON B.TableA_FK = A.ID
GROUP BY A.Name
ORDER BY A.Name

This should do it, in addition to be DBRM independant.

SELECT A.Name
    , SUM(CASE WHEN B.Value = 1 THEN 1 ELSE NULL END) AS B_Value_1
    , SUM(CASE WHEN B.Value = 2 THEN 2 ELSE NULL END) AS B_Value_2
    , SUM(CASE WHEN B.Value = 3 THEN 3 ELSE NULL END) AS B_Value_3
    , SUM(CASE WHEN B.Value = 4 THEN 4 ELSE NULL END) AS B_Value_4
    , SUM(CASE WHEN B.Value = 5 THEN 5 ELSE NULL END) AS B_Value_5
    , SUM(CASE WHEN B.Value = 6 THEN 6 ELSE NULL END) AS B_Value_6
    , SUM(CASE WHEN B.Value = 7 THEN 7 ELSE NULL END) AS B_Value_7
    , SUM(CASE WHEN B.Value = 8 THEN 8 ELSE NULL END) AS B_Value_8
    , SUM(CASE WHEN B.Value = 9 THEN 9 ELSE NULL END) AS B_Value_9
FROM A
    INNER JOIN B ON B.TableA_FK = A.ID
GROUP BY A.Name
ORDER BY A.Name

回复收藏 0 原文

~没有更多了~