SQL Server:将多行合并为一行 [英] SQL Server: Combine multiple rows into one row

问题描述

我查看了其他一些类似的问题，但没有一个适合我所处的特定情况.

I've looked at a few other similar questions, but none of them fits the particular situation I find myself in.

我是 SQL 的相对初学者.

我正在编写查询以创建报告.我对该数据库具有只读访问权限.我正在尝试将三行合并为一行.任何只需要读取访问权限的方法都可以使用.

I am writing a query to create a report. I have read-only access to this DB. I am trying to combine three rows into one row. Any method that only requires read access will work.

话虽如此，我拥有的三行是通过一个很长的子查询获得的.这是外壳:

That being said, the three rows I have, were obtained by a very long sub-query. Here is the outer shell:

SELECT Availability,
       Start_Date,
       End_Date
FROM (
  -- long subquery goes here (it is several UNION ALLs)
  ...
) AS dual

这里是行:

Availability | Start_Date | End_Date
-------------------------------------
99.983       | NULL       | NULL
NULL         | 1/10/2013  | NULL
NULL         | NULL       | 1/28/2013

我想要做的是将三行合并为一行，如下所示:

What I am trying to do is combine the three rows into one row, like so:

Availability | Start_Date | End_Date
-------------------------------------
99.983       | 1/10/2013  | 1/28/2013

我知道我可以使用 COALESCE() 将它们放在一列中，但我更愿意保留三个单独的列.

I am aware that I could use COALESCE() to put them in one column, but I would prefer to keep the three separate columns.

我无法创建或使用存储过程.

可以这样做吗?我可以举个一般情况的例子吗?

Is it possible to do this? Can I have an example for the general case?

推荐答案

您是否尝试过使用聚合函数:

Have you tried using an aggregate function:

SELECT max(Availability) Availability,
       max(Start_Date) Start_Date,
       max(End_Date) End_Date
FROM (
  -- long subquery goes here (it is several UNION ALLs)
  ...
) AS dual

如果你有额外的列，那么你需要添加一个GROUP BY子句

If you have additional columns, then you would need to add a GROUP BY clause

这篇关于SQL Server:将多行合并为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！