Union，Join，或GroupBy

  • Z时代
  • 2024-01-10
  • 分类：问答

我有程序从各种来源提取数据给我3非常相似的表。Union，Join，或GroupBy

Metric   | Tickets |Band 
______________________________________ 
Acknowledgement | 45  | New 
Acknowledgement | 23  | Within 
Acknowledgement | 16  | Near 
Acknowledgement | 2   | Very Near

而且

Metric  | Tickets |Band 
___________________________________ 
Escalation | 10  | New 
Escalation | 43  | Within 
Escalation | 81  | Near 
Escalation | 6   | Very Near

而且

Metric| Tickets |Band 
___________________________________ 
Fixed | 34  | New 
Fixed | 52  | Within 
Fixed | 36  | Near 
Fixed | 4   | Very Near

现在，我想将它们以某种方式结合在一起有一个输出这样

Metric   | New | Within | Near | Very Near 
_____________________________________________________ 
Acknowledgement | 45 | 23  | 16 | 2 
Escalation  | 10 | 43  | 81 | 6 
Fixed   | 34 | 52  | 36 | 4

如何我可以在MS SQLServer中实现这一点，请求se？

回答：

这是一个假的主轴，应该得到你想要的。然后，您将为每个表合并此查询。

SELECT 
    Metric, 
    MAX(CASE Band WHEN 'New' THEN Tickets ELSE '' END) New, 
    MAX(CASE Band WHEN 'Within' THEN Tickets ELSE '' END) Within, 
    MAX(CASE Band WHEN 'Near' THEN Tickets ELSE '' END) Near, 
    MAX(CASE Band WHEN 'Very Near' THEN Tickets ELSE '' END) [Very Near] 
FROM 
    table 
GROUP BY 
    Metric 
UNION 
...

回答：

这不需要重复所有的骨料和CASE表达式为每个表的 - 只是一个简单的工会会做，那么你就可以转动过这一点。

SELECT Metric, [New], [Within], [Near], [Very Near] FROM 
(
      SELECT Metric, Tickets, Band FROM dbo.table_a 
    UNION ALL SELECT Metric, Tickets, Band FROM dbo.table_b 
    UNION ALL SELECT Metric, Tickets, Band FROM dbo.table_c 
) 
AS x PIVOT 
(
    MAX(Tickets) FOR Band IN ([New],[Within],[Near],[Very Near]) 
) AS p;

以上是 Union，Join，或GroupBy 的全部内容， 来源链接： utcz.com/qa/261058.html

回到顶部