如何在SELECT部分​​中包含BIT类型列而不将其包含在T-SQL中的GROUP BY上？

Here is my T-SQL query

这是我的T-SQL查询

SELECT 
    ProductID,
    VendorID,
    ProductName= MAX(ProductName),
    VendorName = MAX(VendorName),
    IsActive = MAX(IsActive) # This brings error 
FROM ProductVendorAssoc 
GROUP BY  
    ProductID,
    VendorID

I want to apply GROUP BY only for the ProductID and VendorID fields, but need to populate the ProductID, VendorID, ProductName, VendorName, IsActive fields.

我想仅为ProductID和VendorID字段应用GROUP BY，但需要填充ProductID，VendorID，ProductName，VendorName，IsActive字段。

Here I used the agreggate function MAX(ProductName) to avoid ProductName in the group by list.

在这里，我使用agreggate函数MAX（ProductName）来避免分组中的ProductName。

But the same trick is not working for BIT columns as operand data type bit is invalid for max operator.

但是同样的技巧不适用于BIT列，因为操作数数据类型位对于max运算符是无效的。

How can i include BIT type column in SELECT part with out including it on the GROUP BY?

如何在SELECT部分​​中包含BIT类型列而不包括在GROUP BY上？

Update.

更新。

What should I need to do if i need to include an INT column like UserID in SELECT in the same way

如果我需要以相同的方式在SELECT中包含像UserID这样的INT列，我该怎么办？

3 个解决方案

#1

Put a CASE expression in there, or convert it to int:

在其中放置一个CASE表达式，或将其转换为int：

IsActive = MAX(CASE WHEN IsActive=1 THEN 1 ELSE 0 END)

or,

要么，

IsActive = MAX(CONVERT(int,IsActive))

You should also be aware, obviously, that this means that the values in the ProductName, VendorName and IsActive columns in the result set may all come from different rows in the base table.

您还应该意识到，这意味着结果集中ProductName，VendorName和IsActive列中的值可能都来自基表中的不同行。

---

If you want those three columns to actually all be from the same row (and assuming SQL Server 2005 or later), you'd do something like:

如果您希望这三列实际上都来自同一行（并假设SQL Server 2005或更高版本），您可以执行以下操作：

;With Numbered as (
    SELECT *,ROW_NUMBER() OVER (
        PARTITION BY ProductID,VendorID
        ORDER BY /* Something appropriate, or if we just want random... */ newid()) as rn
    FROM ProductVendorAssoc
)
select
    ProductID,
    VendorID,
    ProductName,
    VendorName,
    IsActive
FROM Numbered where rn=1

#2

Shorter way to do this:

更短的方法：

IsActive = MAX(0+IsActive)

#3

In SQL2005/2008 this would be look like this:

在SQL2005 / 2008中，这将是这样的：

select ProductId, VendorId, ProductName, VendorName, IsActive
from
(
    select *, row_number() over (partition by ProductId, VendorId order by ProductId) RowNumber
    from Production.Product
) tt
where RowNumber = 1